extract city & state from a city, state, zip cell
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.
Best Answer
-
@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.
Answers
-
@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.
-
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!!
