How to Parse Values in One Cell into Two Cells

[lyndy256]

Hello,

I have a worksheet with City and State in One cell. I would like to extract the state into a separate cell for easier analysis. Is there a formula to do this?

See attached worksheet.

[amy_d2]

Hi Lyndy256,

you can use below formula in Cell B2, & then Copy down till end of your data


=IF(ISERROR(FIND(" ",A2,1)),"",RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

[:) Sixthsense :)]

In B2 Cell

=IFERROR(LEFT(A2,FIND("  ",A2)-1),A2)

In C2 Cell

=IFERROR(TRIM(MID(A2,FIND("  ",A2)+1,255)),"")

Drag both the formula's down...

[lyndy256]

Hi,

It works great! The only thing is that the state abbreviation is still left in column A so I have to go back and delete that but this still helps tremendously.

Thank you!

Lyndy256

[amy_d2]

Hi Lyndy256,

use code provided by sixthsense, in two column it will separate City in one column & state in another

do not do manual work

[lyndy256]

Thanks, Sixthsense for the whole fix!

Lyndy