Aniruddah Alam
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"))
- Now, use the Fill Handle to auto-fill 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.
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
- 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
- How to Lookup County from Zip Code in Excel
- Map Excel Data by ZIP Code (2 Easy Methods)
- How to Convert Zip Code to State with VLOOKUP in Excel
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.
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.
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 (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.
