Function Problems

How to Convert ZIP+4 Codes to Five-Digit ZIP Codes in Excel

Muhammad Talha Oct 7, 2026 870 views

Question details

The user needs to convert 9-digit ZIP+4 codes into standard 5-digit ZIP codes in bulk.

How to Convert ZIP+4 Codes to Five-Digit ZIP Codes in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Formatting address data where ZIP codes currently include a hyphen and a four-digit extension (e.g., 70711-2398) that needs to be removed for standardization.
Observed behavior: The current dataset contains extra digits. When attempting to remove them manually or by splitting, Excel often removes leading zeros from ZIP codes starting with zero.

Before you start

Before converting your ZIP codes, ensure that the destination column is formatted as 'Text' rather than 'General' or 'Number'. This prevents the spreadsheet program from dropping any leading zeros from your newly converted five-digit ZIP codes.

Solution 1Recommended

Use a Formula to Extract and Format (Recommended)

This method safely extracts the first 5 digits into a new column and forces a text format to guarantee that no leading zeros are lost.

By combining the LEFT function with the TEXT function, you can grab exactly what you need while telling the software to maintain a strict five-digit display format.

Select a Blank Cell

Click on an empty cell in a new column directly next to your first ZIP+4 code (for example, cell D2).

Enter the Extraction Formula

Type the formula =TEXT(LEFT(C2,5),"00000") into the cell, assuming your original ZIP code is in cell C2. Press Enter.

Apply to the Entire Column

Click the cell containing your new formula, then double-click the small green fill handle in the bottom-right corner to automatically copy the formula down to the rest of your data rows.

Use a Formula to Extract and Format (Recommended)

How this formula works: The LEFT function grabs the first 5 characters from the left side of your cell. The TEXT function with "00000" ensures that if a ZIP code like 01234 is extracted, the leading zero isn't deleted.

Solution 2

Use Find and Replace for Quick Bulk Deletion

A fast, destructive method that overwrites the original data by removing the hyphen and everything after it.

Solution 3

Use the Text to Columns Feature

Splits the original 9-digit ZIP code into two separate columns using the hyphen as the dividing line.

Effortless Data Cleanup in WPS

Manage and Format Datasets Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful data tools, including Text to Columns, Find & Replace, and advanced text formulas, to help you clean up ZIP codes and other complex data instantly.

1. Open Your Data: Launch WPS Spreadsheet and open the file containing your ZIP codes.
2. Enter the Conversion Formula: In an adjacent blank column, type the formula =TEXT(LEFT(A2,5),"00000") and press Enter.
3. Fill the Column: Double-click the fill handle in the corner of the cell to instantly apply the format to all your addresses.

100% compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Supports all standard data cleanup formulas including LEFT, RIGHT, and TEXT.Processes large datasets quickly without lagging or crashing.Familiar user interface makes transitioning completely seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my ZIP codes lose the leading zero after conversion?

Spreadsheet software treats cells formatted as 'General' or 'Number' as mathematical values, automatically stripping mathematical leading zeros. You can fix this by formatting the column as 'Text' before editing, or by using the TEXT formula to force a specific character format.

Will the Find and Replace method delete hyphens in other columns?

Yes, if you execute a Replace All without selecting a specific range, it will apply to the entire worksheet. Always highlight only the ZIP code column before pressing Ctrl+H to restrict the changes to that specific data.

How do I keep the 4-digit extension instead of deleting it?

You should use the 'Text to Columns' feature under the Data tab. By choosing 'Delimited' and setting the hyphen as your delimiter, the software will split the ZIP code into two separate columns: one for the 5-digit code and one for the 4-digit extension.

Does WPS Spreadsheet support the same formulas as Excel for this task?

Yes, WPS Spreadsheet supports all the exact same text manipulation functions as Excel, including LEFT, MID, RIGHT, and TEXT. You can use the identical syntax without making any changes.

Excel & Data Problems