Excel: Remove the Last Four Digits from ZIP+4 Codes
Question details
The user needs to convert extended ZIP+4 codes into five-digit ZIP codes while preserving the original ZIP+4 values in a separate column.
- Product
- Microsoft Excel or WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains ZIP+4 codes, and the user wants to create a separate column containing only the first five digits for export or further processing.
- Observed behavior
- The source data contains extended ZIP+4 values, but the desired output is a five-digit ZIP code in a separate column.
Identify the column containing the ZIP+4 values and choose an empty column for the five-digit results. Confirm that the source values are stored as text or include the hyphen so leading zeros can be preserved.
Use LEFT to extract the five-digit ZIP code in a new column
Use this method when you need to keep the original ZIP+4 column unchanged while creating a five-digit export column.
The LEFT function returns the first five characters of each ZIP+4 value. Keeping the formula in a separate column preserves the original source data for reference or future use.
In Excel or WPS Spreadsheet, click the first empty cell beside the ZIP+4 data, such as F2 when the original value is in G2.
Type =LEFT(G2,5) in the selected cell and press Enter. Replace G2 with the actual cell containing the ZIP+4 code.
Select the formula cell and drag its fill handle downward, or copy and paste it into the remaining rows in the result column.
When preparing the file, select the new result column as the ZIP-code field instead of the original ZIP+4 column.
Convert formula results to fixed five-digit values
Use this option when the exported file must contain static ZIP codes instead of formulas.
Extract five-digit ZIP codes in WPS Spreadsheet
WPS Spreadsheet can create a separate five-digit ZIP-code column with the LEFT function, fill the formula through the dataset, and preserve the original ZIP+4 values for reference or export.
- 1. Open the worksheet: Launch WPS Spreadsheet and open the workbook containing the ZIP+4 codes.
- 2. Add a result column: Insert or select an empty column beside the source ZIP+4 column, then click the first result cell.
- 3. Enter the formula: Type =LEFT(G2,5), replacing G2 with the first source cell, and press Enter.
- 4. Fill and format the results: Drag the fill handle down the column, then format the results as Text or with a five-digit format if leading zeros need protection.
Frequently Asked Questions
What formula removes the last four digits from a ZIP+4 code?
Use =LEFT(G2,5) when the ZIP+4 value is in G2. The formula returns the first five characters, such as 12345 from 12345-6789.
How can I preserve the original ZIP+4 values?
Enter the LEFT formula in a separate empty column rather than replacing the source column. Export or use the new five-digit column while retaining the original data.
Why are leading zeros missing from my five-digit ZIP codes?
Spreadsheet applications may treat the result as a number. Format the result column as Text before entering the formula, or apply a custom number format of 00000.
Can I replace the formulas with permanent ZIP-code values?
Yes. Copy the completed result column, select Paste Special, and choose Values. This keeps the five-digit results while removing the formulas.
Can I use this method in WPS Spreadsheet?
Yes. Open the workbook in WPS Spreadsheet, enter =LEFT(source_cell,5) in a new column, fill it down, and save or export the five-digit result column.
