Extract ZIP Code from Address in Excel

Sometimes we need to extract ZIP codes from addresses in order to organize, analyze, sort, and filter data. When addresses are saved in Excel, obtaining ZIP codes is a quick job.

In this article, I am going to show you some easy methods that we can use to extract ZIP codes from addresses in Excel.

Click here to download the example file to follow along

Method 1 – Using Flash Fill

Sometimes we don’t like to use complex formulas for our Excel work. When we want to extract ZIP codes from an address list, we can use the Flash Fill feature to extract ZIP codes.

Below I have some addresses in Column A, and I want to extract the ZIP codes from these in column B.

Address with Zip code

The last 5 digits of each address are the ZIP code of that address.

last 5 digits are ZIP code

I can easily extract all ZIP codes to column B using the Flash Fill Feature.

  1. Go to cell B2 and type the ZIP code of the first address. So, I am typing 94158 in cell B2. I also can copy 94158 (the ZIP code) from cell A2 and paste it on cell B2.
Enter the zip code manually
  1. Go to one cell below the first ZIP code. So, I am going to cell B3.
Select the cell below
  1. Press ‘Ctrl + E‘ to apply the Flash Fill. Then, Excel will quickly extract all ZIP codes to column B.
Flash fill result

Instead of the given shortcut, I can use the Flash Fill icon to apply the Flash Fill in the following ways.

  • Go to the Data tab and click the Flash Fill icon (which is in the Data Tools group).
Flash fill option in the data tab
  • Go to the Home tab. Expand the Fill Options (in the Editing group) and Select the Flash Fill.
Flash fill option in the home tab
Also read: How to Separate Address in Excel?

Method 2 – Using the RIGHT Function

The Flash Fill method is quick and simple to use, but it is not dynamic.

To put it another way, if you change an address in column A, the extracted ZIP code will not be updated.

To get around this, we can use Excel functions and formulas. The Excel RIGHT function is one such tool that we can use to extract ZIP codes from addresses.

Below I have some addresses in Column A, and I want to extract the ZIP codes from these in column B.

Address with Zip code

Now I want to extract ZIP codes (last 5 digits of each address) to column B.

I can use the below formula for that.

=RIGHT(A2,5)
RIGHT function to extract zip codes

The syntax of the RIGHT function is RIGHT(text,[num_chars]). The first argument of the function is text.

I have to select the address as the text. So, I am selecting cell A2 for the first argument.

Then, I have to specify how many characters I want to get from the right side of the selected text.

A ZIP code has 5 digits. So, I want to get 5 characters from the end and I enter 5 for the second argument.

Also read: How to Extract First Name in Excel

Method 3 – Using the TEXTAFTER Function to Extract the ZIP Code from Address in Excel

Click here to download the example file to follow along

We can also use the Excel TEXTAFTER function to extract ZIP codes from addresses.

This is especially beneficial when we find addresses with extended ZIP codes (ZIP+4).

In such cases, we have to extract all of the digits after the last space. The TEXTAFTER function lets us get all digits in an address after the final space.

Below I have some addresses in Column A, and I want to extract the ZIP codes from these in column B.

Address with Zip code

Now I want to extract ZIP codes (all the digits after the last space) to column B.

I can use the below formula for that.

=TEXTAFTER(A2," ",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))
TEXTAFTER function to extract zip code

The syntax of the TEXTAFTER function is TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found]).

Except for the first two arguments of this function, all other arguments are optional.

For the first argument, I have to select the address. So, I am selecting cell A2 for the first argument.

Then, I have to enter the delimiter within quotes. I am entering a space within quotes for the second argument. The third argument is the instance of the delimiter, after which I want to extract the text.

I want to extract the digits after the last space. However, the number of spaces inside a cell is changing one cell to another. So, I cannot give a fixed number for the third argument.  

To find the instance number of the last space, I am using the below formula.

LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))

The LEN function helps to get the number of characters in a cell. The LEN(A2) formula gives me the number of characters of each cell.

Then, using the LEN(SUBSTITUTE(A2,” “,””)) formula, I get the number of characters without spaces. The difference between LEN(A2) and LEN(SUBSTITUTE(A2,” “,””)) gives me the number of spaces or the occurrence of the last space.

Note: The TEXTAFTER function is currently available for Microsoft 365 users only. If we try to open an Excel file that contains the TEXTAFTER function in a earlier version of Excel, we will get the #NAME? error.

Also read: Extract Last Name in Excel

Method 4 – Using the TEXTSPLIT and CHOOSECOLS Functions (available in Microsoft 365)

I believe that the new text functions of Microsoft Excel are super useful on many occasions.

We can use the Excel TEXTSPLIT and CHOOSECOLS functions also to extract ZIP codes from addresses in Excel.

Below I have some addresses in Column A, and I want to extract the ZIP codes from these in column B.

Address with Zip code

Now I want to extract ZIP codes (all the digits after the last space) to column B.

I can use the below formula for that.

=CHOOSECOLS(TEXTSPLIT(A2," "),COUNTA(TEXTSPLIT(A2," ")))
CHOOSECOLS function to extract zip code

First, I am using the TEXTSPLIT function to split the address at each space. The syntax of the TEXTSPLIT function is TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with]). I am selecting the address cell for the first argument and entered a space within quotes for the second argument as the delimiter.  So, if I use only the TEXTSPLIT function, each address is split like below.

TEXTSPLIT function to split the address

Then, I am using the CHOOSECOLS function to get only the last column of the split text. The syntax of the CHOOSECOLS function is CHOOSECOLS(array,col_num1,[col_num2],…). But, I cannot fix the last column number as it is not the same for all addresses. So, I am using the COUNTA function to find the number of columns after using the TEXTSPLIT function. The below formula helps to get the last column number of each split address.

COUNTA(TEXTSPLIT(A2," "))

CHOOSECOLS and TEXTSPLIT functions are currently available for Microsoft 365 users only.

Also read: Extract Last Word in Excel

Method 5 – Using the MID Function to Extract the ZIP Code from Address in Excel

Some Excel users are still not using the Excel 365 version. Then they can’t use the last two methods because they only work with the Microsoft 365 version.  

Because of that, I’m going to show you another Excel formula that any Excel user can use. This formula is somewhat complicated. But, all Excel versions are supported.

Below table’s column A shows some addresses that I have taken from a website.

Address with Zip code

Now I want to extract ZIP codes (all the digits after the last space) to column B.

I can use the below formula for that.

=MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,5)
MID function to get the zip code

The syntax of the MID function is MID(text,start_num,num_chars). I am selecting the address cell for the first argument of the function.

For the second argument of the MID function, I have to enter the position of the first extracting character.

I am using the below formula to find the position of the last space.

FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

Then, I am adding 1 to the above formula, as I want to extract the first character after the last space.

For the final argument of the MID function, I have to specify the number of characters that I want to extract. I am entering 5 for that.

Now, you have learned different methods to extract ZIP codes from addresses in Excel.

When the format of addresses is changing, you can do some small changes to the above formulas and extract ZIP codes.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment