worksheet function - How to extract address in excel?

Question

I have addresses in one cell and I want to extract them in different cells on the same row. Some cells have four lines of address and some have three. I am able to easily split using text to column and various delimiters for the ones with three but not the ones with four.

enter image description here

In the first example I have four lines and second has three

Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage, AK 99508 US

I would like the above as split into 5 cells. One cell each for address, City, State, Zip code and country

Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2


Anchorage

AK 

99508 

US

in second example below

Providence Alaska Medical Center
3200 Providence Drive
Anchorage, AK 99508 US

I would like

Providence Alaska Medical Center
3200 Providence Drive

Anchorage

AK 

99508 

US

Could this be done using a formula?

What I have done so far is that the full address is in cell A1 and I want them in B1, C1, D1, E1, and F1. What i have done is for the country I use =RIGHT(A2,2), zip code, i use =MID(A2, LEN(A2)-7, 5), state =MID(A2, LEN(A2)-10, 2). Now I am trying to extract the city. The city is before the comma and after the line break (Char(10)) and Address is first 2 or 3 lines. I don't know how to do that.

There is a line break between each line.

Thank you

Answer 1

If you use SUBSTITUTE() you can substitute the n'th occurrence of a character with a new character, then use FIND() to return that character. For example, if you SUBSTITUTE your CHAR(10), the third occurrence, you can find that character again that would be the end of the address.

So if your FULL address is in A1, then you could extract the address with LEFT(A1,FIND("~",SUBSTITUTE(A1,CHAR(10),"~",3)))