how to un-concatenate address?

[sudkool]

Hi,
I have full address in once cell and I was just wondering if there any way to un-concatenate it in all different cells. Here is the example

Cell A1

16545 Dingo St
suite 656
Chicago, IL 54545

Please help me if you know how to save all of that cell into 5 different cells (address1, address2, city, state, address).

Thank you so much in advance

[Bryan Hessey]

Hi,
I have full address in once cell and I was just wondering if there any way to un-concatenate it in all different cells. Here is the example

Cell A1

16545 Dingo St
suite 656
Chicago, IL 54545

Please help me if you know how to save all of that cell into 5 different cells (address1, address2, city, state, address).

Thank you so much in advance

Hi,

one way, in B1:I1 put

=LEFT(A1,FIND("@",A1)-1)
=TRIM(MID(A1,FIND("@",A1)+1,LEN(A1)))
=LEFT(C1,FIND("@",C1)-1)
=TRIM(MID(C1,FIND("@",C1)+1,LEN(C1)))
=LEFT(E1,FIND(",",E1)-1)
=TRIM(MID(E1,FIND(" ",E1),LEN(E1)))
=LEFT(G1,FIND(" ",G1)-1)
=TRIM(MID(G1,FIND(" ",G1),LEN(G1)))

and replace @ with Alt/Enter

Formula fill this to the extent of your data, and when all appears correct, select the AllData and Copy, then Paste Special = Values back over the data.

Columns G E C and A can then be deleted.

hth
---