Address Cleanup

[msquared99]

I have a spreadsheet that is several thousand rows long. It contains addresses and they look like this:

In the cell: 600 Rutherford Ave NESomewhere, VA 24018

In the formula bar:
600 Rutherford Ave NE
Somewhere, VA 24018

What I want to do is put the street address in say column B and the city, state and zip in column C.

Any advice?

Thanks!

 

[chirayu]

Its easy enough to separate the zip as there's a comma there so we can use a mid formula for that, but separating the somewhere is probably not possible

 

[Deepak]

There are several ways to do the same

Assume your address in A1 then

B1 =LEFT(A1,FIND(CHAR(10),A1)-1)

C1 =MID(A1,FIND(CHAR(10),A1),99)


Check attached xl for some more ways to split the text.

 

[msquared99]

Pretty slick Deepak!

I have some cells where there are 3 lines.
Example:
Cell View:
Accounts Payable 21420 East Main StreetSomwhere, VA 24012

Formula Bar View:
Accounts Payable
21420 East Main Street
Somewhere, VA 24012

 

[chirayu]

Deepak. Good solution. But again, assumes that there are commas between each set. By looking at the data provided by msquared99 theres only 1 comma before the zip. Hence my previous answer of MID(Find

 

[Deepak]

Pretty slick Deepak!

I have some cells where there are 3 lines.
Example:
Cell View:
Accounts Payable 21420 East Main StreetSomwhere, VA 24012

Formula Bar View:
Accounts Payable
21420 East Main Street
Somewhere, VA 24012

Would you pls upload a sample workbook with different address layout.

 

[msquared99]

Hey Deepak,

I was able to modify the formula to get it to work.

Thanks a bunch!