Splitting text string from 1 cell into 2 cells

[ExcelNonGuru]

Here is my situation: I have a few thousand lines of data, so I need a way to automate this or I will be here til I retire!


One of the columns has an address (123 Main St Anytown CA) which is followed by a contact person (not sure why the guy who made this put all that in one cell). All of the addresses end in "CA", so my question is: Is there a way to separate the address and the contact person, using "CA" as the common divider?


123 Main St Anytown CA Bob Smith (213)555-1212 (current layout)

123 Main St Anytown CA | Bob Smith (213)555-1212 (how I need it to be)


Oh and if it matters, I am using Excel 2010


Thank you-


The NonGuru

 

[3G]

Is the "|" another cell, or did you want to split it IN cell?


If IN cell, assume text is in B2

In B3:=FIND("CA",B2)

In B4: =LEFT(B2,B3+1)&" | "&RIGHT(B2,B3+2)


not sure how you can join these two formulas, but coincidentally, I was just practicing this stuff!

 

[oldchippy]

Hi,


If you want them in separate cells then in


B1 =LEFT(A1,FIND(" CA ",A1)+3)


and


C1 =RIGHT(A1,LEN(A1)-(FIND(" CA ",A1)+3))

 

[ExcelNonGuru]

Sorry my "|" meant a new cell... Should have stated that on the onset. Yes I want to separate the text string in one cell into 2 different cells.

 

[ExcelNonGuru]

Yay! Thank you chippy! That worked perfectly!