Forum Discussion
Text to Column
Hello Everyone.
I'm trying to split data into 2 columns. The original data is made up of cities and states:
Notice that some items have a comma and others don't. Also, the cities states are different lengths. So, I cannot use a space or a comma as a delimiter. I'm trying to figure out how to separate them. I came up with this formula to add a colon so I can use the delimiter to separate them.
The problem then becomes the fact that I have to count the number of spaces in each city to add the colon. Then, I have to copy and paste special as values. Also, notice that in New Orleans (for example), it keeps the comma:
One more issue, if I get a new report with added cities and states, then I have to re-do the calculations.
HELP!!!!
Balta
1 Reply
You don’t need to count spaces or manually insert delimiters at all. Since the state is always the last “word”, you can extract it dynamically and everything before it becomes the city.
Formula approach (no helper columns needed)
Assuming your data is in A1.
City (Column B):
=LEFT(A1, FIND("@", SUBSTITUTE(A1, " ", "@", LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) - 1)
State (Column C):
=RIGHT(A1, LEN(A1) - FIND("@", SUBSTITUTE(A1, " ", "@", LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Even better for Excel 365 & 2021
City
=TEXTBEFORE(A1," ",-1)
State
=TEXTAFTER(A1," ",-1)
These are much cleaner and automatically split at the last space.
If you want remove commas…
To clean up cases like "New Orleans, LA":
Wrap the city formula with:
=SUBSTITUTE(LEFT(A1, FIND("@", SUBSTITUTE(A1, " ", "@", LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) - 1), ",", "
Better if you use Power Query solution for the next time, that makes this completely automatic for future reports.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
