Forum Discussion

BaltaD's avatar
BaltaD
Copper Contributor
Mar 27, 2026

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

excel

3 Replies

  • IlirU's avatar
    IlirU
    Iron Contributor
    Apr 01, 2026

    BaltaD​,

    I created a small dataset that contains the full names of the U.S. states in column A, and their corresponding USPS state postal codes in column B. I sourced these values from Wikipedia’s list of U.S. states and abbreviations.

    In column D, I inserted your data, which contains combined “City + State” strings. These entries include either the full state name or the state postal code.

    To split each entry into two separate columns, one for the city and one for the state, I used the following formula in cell F1:

    =LET(
  us_state, A2:B51,
city_state, D2:D5,
        lm, LAMBDA(a,b, SUBSTITUTE(a, b, "")),
        rd, REDUCE(city_state, us_state, lm),
            VSTACK({"City","State"},
                   SUBSTITUTE(HSTACK(rd, REDUCE(city_state, rd, lm)), ",", ""))
)

    This formula works by first removing all state names and postal codes from the original “City + State” strings, leaving only the city names. Then it separates the state part by subtracting the extracted city from the original text. Finally, it stacks everything into a two‑column output labeled City and State.

    Hope this helps,

    IlirU

  • BaltaD's avatar
    BaltaD
    Copper Contributor
    Mar 30, 2026

    Thank you Nicolino.  I will try it and see how it works out.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Mar 29, 2026

    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.