Sorting/Converting Street Addresses

[dmontez]

I have street addresses in one column. Example 1234 E Fake St, Unit 102

I need to seperate the 1234 E into one column then the Fake St into another and then Unit 102 into it's own aswell. I know how to use Text To Columns but it will only allow me to use space with ends up with everthing in its own column. 1234 E Fake St Unit 102

I dont want that cause then I have to put the ones I need together back together.

Thanks!

 

[sulakvea]

text to columns does allow you to choose one of several delimiters - space, comma, tab, and other

just select Delimited instead of Fixed Width in step 1, and then keep clicking Next button instead of Finish - it will take you thru that step

 

[Robert Mika]

Try:
Your data in A1 then:

B1:=TRIM(LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",2))))
C1:=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),MID(A1,FIND(",",A1),LEN(A1)),""))
D1:=TRIM(MID(A1,FIND(",",A1)+2,LEN(A1)))