Divide Address into separate columns

Hello all,

I need to take the following data in a single cell ....
248 Twentyfirst St, Renmark SA 5341

and change it into this.....

248 Twentyfirst St | Renmark | SA | 5341 |

So I end up with 4 columns. What I have been doing up to now is copy the column with data into a text file, then import back using spaces as delimiters, then I spend a heap of time fixing the errors.

Presume the data is in column A, Is there a way to put the postcode into column D, the State into Column C, the name into column B and leave the address in column A? - Wow! this would help so much, I have 1000's to do! - Maybe I need to use the spaces as identifiers?

I did manage to move the postcode across... but I keep getting lost when I need to get more specific. Any help would be appreciated heaps!

There is no easy way to do this, but this should give you a start:
There are several caveats attached your data must be set up as shown:

PHP Code: 

                 A
1) 248 Twentyfirst St, Renmark SA 5341
2) 9 State Street, Washington DC 02969
3) 300 W. Broadway, Philadelphia PA 12345
4) 200 North Abby St, St. Clair MN 01101 


with a comma following the street address, if not then all bets are off.
Cities with names seperated by a space will not work.

First select your data and do a Text To Columns using the comma as a delimiter to separate out the street address from the city, state, zip.

Your new data should now look like this:

PHP Code: 

            A                    B
1) 248 Twentyfirst St      Renmark SA 5341
2) 9 State Street          Washington DC 02969
3) 300 W. Broadway         Philadelphia PA 12345
4) 200 North Abby St       St. Clair MN 01101 


Make sure there are no hidden spaced in column B, do a =TRIM() to remove any extra space characters.

PHP Code: 

            A                    B                     C
1) 248 Twentyfirst St      Renmark SA 5341          =TRIM(B1)
2) 9 State Street          Washington DC 02969      =TRIM(B2)
3) 300 W. Broadway         Philadelphia PA 12345    =TRIM(B3)
4) 200 North Abby St       St. Clair MN 01101       =TRIM(B4) 


You now have this set up

PHP Code: 

            A                    B                     C
1) 248 Twentyfirst St      Renmark SA 5341          Renmark SA 5341      
2) 9 State Street          Washington DC 02969      Washington DC 02969  
3) 300 W. Broadway         Philadelphia PA 12345    Philadelphia PA 12345
4) 200 North Abby St       St. Clair MN 01101       St. Clair MN 01101 


In column D enter the formula:
=LEFT(C1,FIND(" ",C1,1))

In column E enter the formula:
=LEFT(RIGHT(C1,LEN(C1)-FIND(" ",C1,1)),FIND(" ",RIGHT(C1,LEN(C1)-FIND(" ",C1,1)),1))

In column F enter the formula:
=RIGHT(C1,LEN(C1)-FIND("#",SUBSTITUTE(C1," ","#",LEN(C1)-LEN(SUBSTITUTE(C1," ","")))))

Your data should now look like:

PHP Code: 

         C                      D            E         F
1) Renmark SA 5341          Renmark         SA        5341
2) Washington DC 02969      Washington      DC       02969
3) Philadelphia PA 12345    Philadelphia    PA       12345
4) St. Clair MN 01101       St.             Clair    01101 


As you can see on row 4 St. Clair MN get's messed up so you will have to do some clean up work.

Once you have things neat and tidy, then do a Copy/Paste Values and delete columns B & C

It does not do it all, but it should give you at least a fighting chance.

hello xenixman,

Ah! - so wonderful to see your notes above, I am about to try it now.
I have been on this list for over 4 hours, and yes, I have a comma separator that I can use.
Will let you know shortly. - Thanks again.

SUCCESS! - I have just done in 6 minutes that I couldn't do in 6 hours. Now the seed is planted, up is the only way - thanks xenixman .