How to Concatenate ZIP Codes in Excel (3 Easy Methods)

ZIP Code is one kind of Postal Code that is used to identify and locate an address. To keep location data, we have to store ZIP code data frequently in Excel. As the new format of ZIP Code consists of 9 numbers instead of 5, the extra 4 digits frequently need to be concatenated with the old 5 digits. In this article, I will discuss how we can concatenate ZIP codes in Excel.

How to Concatenate ZIP Codes in Excel: 3 Easy Methods

In this section, we will demonstrate 3 effective methods to concatenate ZIP codes in Excel. For illustration, I have taken a sample data set where in one column we have the old ZIP codes (5 digits) and in another column, we have +4 Code (newly added one). So we will try to add/ concatenate those two pieces to form the newly formatted 9 digits ZIP+4 Code.

For example, on the first row, the concatenated ZIP+4 code will look like 12786-9842. Two do that, let’s look at our first method.

1. Use a Combination of TEXT & CONCAT Functions to Concatenate ZIP Codes

In the first method, we will use the TEXT and the CONCAT functions to perform our task. The TEXT function will allow us to format the string and the CONCAT function will join the strings. To know more, follow the steps below.

Steps:

In cell E5, write down the following formula and press Enter. You will see that the two pieces have been combined to form ZIP+4 formatted code.

=CONCAT(TEXT(C5,"00000"),TEXT(D5,"-0000"))

Now, use the Fill Handle to Autofill the rest of the cells and get the complete result.

🎓How Does the Formula Work?

TEXT(D5,”-0000″)

The TEXT function will take the string from cell D5 (9842) and convert it into -9848

TEXT(C5,”00000″)

The TEXT function will take the string from cell C5 (12786) and convert it into 12786

CONCAT(TEXT(C5,”00000″),TEXT(D5,”-0000″))

Here the CONCAT function will join the 12786 and -9848 and make it 12786-9848.

2. Concatenate ZIP Codes with Ampersand Operator

Instead of using the CONCAT function, we can simply use the Ampersand operator (&) to achieve our goal. To do that, follow the steps below.

Steps:

On cell E5, write down the following formula and press Enter. You will get the desirable result.

=C5&"-"&D5

Now, if you Autofill the rest of the cells with Fill Handle, you will get the complete result.

3. Applying TEXTJOIN Function for Concatenating ZIP Codes

We can use the TEXTJOIN function as well to concatenate ZIP codes. To know more, follow the steps below.

Steps:

On cell E5, write the following formula and press Enter Consequently, you will see the concatenated ZIP+4 code.

=TEXTJOIN("-",FALSE,C5:D5)

Here,

“–” is the separator.
FALSE is for taking account of an empty cell.
C5:D5 is the range of cells that are to be joined.

Then, use the Fill Handle to Autofill the rest of the cells.

How to Format ZIP Code in Excel

Though it is not known to many of us, Excel does provide two built-in formats for expressing the ZIP code (Both 5 digits and 9 digits formats). To illustrate that, suppose we have a set of zip codes like this.

Now to represent those codes in proper formatting, follow the steps below.

Steps:

First, select the total column of the ZIP code and go to Number Format from the ribbon, and from the drop-down option (shown in the figure below), choose More Number Formats.

Now select Special > ZIP Code +4 (as in this case the number are in 9 digits. For 5 digits format, we need to choose ZIP Code). Then click

As a result, you will see that the ZIP Codes are now in the proper formatting.

Read More: How to Format Zip Codes in Excel

Things to Remember

As Excel usually removes the leading zero, if you have any leading zero in your ZIP Code, you have to format it as text in order to display the leading zero.
If you use an earlier version of Excel other than Microsoft 365, then use the CONCATENATE function instead of the CONCAT function.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Concat ZIP Code.xlsx

Conclusion

That is the end of this article. If you find this article helpful in understanding how to concatenate ZIP codes in Excel, please share this with your friends. Moreover, do let us know if you have any further queries.

<< Go Back to Zip Code in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!