extract city & state from a city, state, zip cell

Kayla

I want to extract the city & state from a cell that has the city, state and zip code.

The zip code may not always be 5 digits (either 90210 or 90210-1234) and the City and State may not always have a comma separating them.

A formula that can extract any text left of a numeric value is what would solve this, but I cannot figure it out or find anything in other posts.

Here is an example of what I'd want to return out of the first column.

Lucas Rayala

@Kayla cleaning messy data is messy. One trick is to get rid of that number using this formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Address@row, "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", ""), "0", ""), "-", "")

Test this and see what your new data looks like, then build on this to start to parse out your other needs.

Lucas Rayala

@Kayla cleaning messy data is messy. One trick is to get rid of that number using this formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Address@row, "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", ""), "0", ""), "-", "")

Test this and see what your new data looks like, then build on this to start to parse out your other needs.

Kayla

Worked perfect except the last one, adding in that comma BUT those should be far and few between, that I'm running with this. Thank you!!