Function Problems

How to Combine ZIP Code and ZIP+4 in Excel with a Formula

Elise Williams Oct 20, 2026 868 views

Question details

The user needs to merge a 5-digit ZIP code and a 4-digit ZIP+4 extension into a single cell formatted with a hyphen, ensuring that any leading zeros are retained in the final output.

How to Combine ZIP Code and ZIP+4 in Excel Using a Formula

Product: Excel
Device & OS: not provided
Scenario: Formatting and combining two separate columns of postal data into a standard 9-digit ZIP code format.
Observed behavior: Standard merging methods drop leading zeros, requiring a specific formula to preserve the 00000-0000 format.

Before you start

Ensure your 5-digit ZIP codes and 4-digit extensions are neatly organized into two separate columns. Verify that there are no hidden spaces in your cells before applying the formula.

Solution 1Recommended

Use the TEXT and Ampersand Formula

This is the most reliable method for all Excel versions. It merges the cells with a hyphen while explicitly enforcing the digit count so leading zeros are never dropped.

Excel naturally drops leading zeros because it treats the postal codes as mathematical numbers. By wrapping the cell references in the TEXT function, you force Excel to display the values as text strings with a guaranteed number of characters.

Select a destination cell

Click on an empty cell in a new column where you want the combined ZIP code to appear.

Enter the formula

Assuming your 5-digit ZIP code is in cell A2 and the 4-digit extension is in cell B2, type the following formula: =TEXT(A2,"00000")&"-"&TEXT(B2,"0000")

Apply the formatting

Press Enter to execute the formula. You should see the merged code, such as 00622-0072.

Fill down the column

Select the cell with your new result, grab the fill handle (the small square at the bottom-right corner), and drag it down to apply the formula to the remaining rows.

Leading Zeros Kept: The "00000" formatting argument ensures that a ZIP code like 622 is correctly padded with zeros to become 00622.

Solution 2

Use the TEXTJOIN Function (Microsoft 365 or Office 2021)

If you are using a newer version of Excel, the TEXTJOIN function provides a cleaner syntax for combining text with delimiters.

Efficient Spreadsheet Software

Easily Combine Data and Formulas with WPS Spreadsheet

WPS Spreadsheet provides robust support for all major text manipulation formulas, including TEXT and TEXTJOIN. You can effortlessly clean, format, and manage your ZIP code lists in a familiar and highly compatible environment.

1. Open your data: Launch WPS Spreadsheet and open the document containing your postal codes.
2. Input the formula: Select a blank cell next to your data and enter =TEXT(A2,"00000")&"-"&TEXT(B2,"0000").
3. Apply and fill down: Press Enter to combine the ZIP code, then double-click the cell's fill handle to instantly apply the formula to the entire column.

Fully compatible with Microsoft Excel (.xlsx) file formats and formulas.Seamlessly processes advanced text functions to handle leading zeros.Lightweight, fast, and completely free for everyday data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my ZIP codes lose leading zeros in Excel?

Excel defaults to a 'General' number format, which automatically removes non-significant leading zeros because it treats the entry as a math value rather than text. Using the TEXT function forces Excel to display the exact number of digits you specify.

How do I handle rows where the ZIP+4 column is empty?

If you only use the standard formula, an empty ZIP+4 cell might leave a trailing hyphen (e.g., 12345-). You can wrap your formula in an IF statement to prevent this: =IF(ISBLANK(B2), TEXT(A2,"00000"), TEXT(A2,"00000")&"-"&TEXT(B2,"0000")).

Can I convert the formula results into regular text so I can delete the original columns?

Yes. Select the cells containing your formula results and copy them (Ctrl+C). Then, right-click on the same selection, choose 'Paste Special', and select 'Values'. This replaces the formulas with static text, allowing you to safely delete the original columns.

Excel & Data Problems