Text to Column for a full word

[yankeekid86]

How can I use a text to column for full words? I would need it not for just one word, but varying words in a column. For example, after a name.


John Doe New York
Mary Smith California
Mike Williams South Carolina

I'd want to insert a word before the state in the end.

Thanks!

[k64]

Did you try using space as a delimiter? If you later want to join the columns you can use the formula =A1&" "&B1.

[Teethless mama]

=REPLACE(A2,LOOKUP(1E+99,SEARCH(State,A2)),,"insertword ")

see the attached file

[Tony Valko]

So, what kind of result are you looking for?

Are you wanting to extract the state name from the cell?

A2 = John Doe New York

Result should be: New York

[yankeekid86]

thanks mama!

[Teethless mama]

You're Welcome!