Jan 15 2020 06:26 AM
Hello @Mike3812,
If you have Excel 2013 or later, you may flash fill the Zip Codes. See how to use flash fill here:
If the Zip Codes are always the last 5 characters in each field, then that could be the following formula:
=RIGHT(Text, 5)
Jan 15 2020 07:27 AM
If you want to parse even more (in this case, isolating the city name) you should familiarize yourself with the set of text functions, including at the very least the following:
LEFT, which can isolate a designated number of characters from the left
RIGHT, same as above from the right
MID, isolates characters from within the middle; you designate start and end point
LEN will calculate the length of a text field
FIND will find a given character in a text field, beginning at point x
So if there's general consistency in your field, it would be possible even to isolate the city name. If (this is where consistency matters) commas are used to separate street from city and city from zip, and zip is always 5 characters long, something like the following could work to parse out city name from your text (assuming it's in cell C5):
=MID(C5,FIND(",",C5)+2,(FIND(",",C5,FIND(",",C5)+1)-FIND(",",C5))-2)
I just tested this formula successfully on a street address in NYC, where the city name consists of two words..... The formula below also works, coming at it from a different direction (and assuming zip is always the last 5 characters):
=RIGHT(LEFT(C5,LEN(C5)-7),LEN(C5)-FIND(",",C5)-8)
There are two basic points that need to be made here: