excel -

Copper Contributor

how to parse data in a field i.e. field contains:  address, city and zip code - i want to isolate ZIP code in a new cell

2 Replies

Hello @Mike3812,

 

If you have Excel 2013 or later, you may flash fill the Zip Codes. See how to use flash fill here:

https://support.office.com/en-us/article/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f7...

 

If the Zip Codes are always the last 5 characters in each field, then that could be the following formula:

=RIGHT(Text, 5)

@Mike3812 

 

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:

  • The text functions are very useful in parsing out fields like the one you are facing, where multiple data elements have been combined into one field
  • More important: it's better database design always to keep distinct data elements in their own distinct fields because there's another text function--CONCATENATE--that easily assembles distinct fields, far easier than the parsing.