Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


3 Useful Methods to Concatenate ZIP Codes in Excel

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"))

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

  • Now, use the Fill Handle to auto-fill the rest of the cells and get the complete result.

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

🎓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.

Read More: How to Format Zip Code to 5 Digits in Excel (5 Easy Methods)


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

Concatenate ZIP Codes with Ampersand Operator

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

Read More: How to Create Zip Code with Excel Formula (6 Easy Ways)


Similar Readings


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.

Applying TEXTJOIN Function for Concatenating ZIP Codes

  • Then, use the Fill Handle to autofill the rest of the cells.

Applying TEXTJOIN Function for Concatenating ZIP Codes

Read More: How to Remove Last 4 Digits of Zip Code in Excel (10 Easy Ways)


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.

How to Format ZIP Code in Excel

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

How to Format ZIP Code in Excel

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

How to Format ZIP Code in Excel

Read More: How to Format Zip Codes in Excel (2 Easy Methods)


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.

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. Finally, please visit ExelDemy for more exciting articles on Excel.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, and explore new things. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo