Excel Formula Help - Extracting Zip codes
In this Microsoft Excel blog, we look at how it is possible to extract US zip codes from a column of addresses. As can be seen below, we have a list of address data with variations on zip code presentation; some use the short hand and others use the longer code version.
To extract the codes, we start by entering the RIGHT formula in the cell where we would like our zip codes to appear. Like it's opposite number, LEFT, the RIGHT formula directs Excel to count the number of characters in a text string from the right (or left, with LEFT).
A clue to the syntax is given once the function is entered; (text,[num-chars]) In other words, RIGHT is asking the user to select the location of the text and then the number of characters it should count.
We select the cell, A2, then count the number of characters used in the zip code, in this case 5. Our formula should now read =RIGHT(A2,5)
Hit return and we will see the zip code appear. Copy all the way down to get the zip codes for the top five addresses:
As we can clearly see, the top five zip codes have been returned without problem. However, the bottom five have been cut in half as the formula only requests the first five characters from the right. We need to go into the first cell with the longer zip codes and change =RIGHT(A2,5) to =RIGHT(A2,10) so that all longer zip code characters are retuned:
Then copy down again.
For more help working with your data in Excel, contact our experts. For further details on the RIGHT function, take a look at this Microsoft Excel help page.
