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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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 Find ZIP Code from Address in Excel


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: [Fixed] Zip Codes in Excel Starting with 0


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 Sort Data by Zip Code in Excel


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


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.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo