Adress split into seperate columns

[Ron Purpura]

Hello All
Looking for some assistance. I have tried Text to columns function, but not working quite the way I thought due to missing commas etc
I am simply looking for splitting an address in a column to seperate columns in to reflect Cite ,state , and zip. Attached is an example
Thank you

[MarvinP]

Hi Ron,

Those line breaks are really Char(10) and you need to split using them. Read this site to see:
https://www.ablebits.com/office-addi...-string-excel/

[63falcondude]

Here are some formulas that work with your limited sample:

C2 (Address) =LEFT(B2,FIND(CHAR(10),B2)-1)

D2 (Address 2) =IF(LEN(B2)-LEN(SUBSTITUTE(B2,CHAR(10),""))=2,TRIM(MID(SUBSTITUTE(B2,CHAR(10),REPT(" ",100)),100,100)),"")

E2 (City) =SUBSTITUTE(TRIM(MID(SUBSTITUTE(B2,CHAR(10),REPT(" ",100)),IF(LEN(B2)-LEN(SUBSTITUTE(B2,CHAR(10),""))=2,200,100),100))," "&F2&" "&G2,"")

F2 (State) =LEFT(RIGHT(B2,8),2)

G2 (Zip) =RIGHT(B2,5)

[Ron Purpura]

Thanks Marvin P and falcon dude ! The formulas are amazing
Much appreciated
Ron

[63falcondude]

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.