How to change 9-digit zip codes to 5 digits in Excel?
Typically, in the United States, the standard zip code format consists of 5 digits. However, certain data lists may contain 9-digit zip codes—commonly formatted as "12345-6789"—which can present challenges if you need only the primary 5-digit code for tasks like postal sorting, reporting, or matching with other databases. What methods can you use to convert a column of these extended zip codes to their 5-digit forms efficiently in Excel?
Change 9-digit zip codes to 5-digit zip codes with a formula
Change 9-digit zip codes to 5-digit zip codes with a useful feature
Change 9-digit zip codes to 5-digit zip codes using Text to Columns
Change 9-digit zip codes to 5-digit zip codes with a VBA Macro
Change 9-digit zip codes to 5-digit zip codes with a formula
When you only want to keep the first 5 digits from a 9-digit zip code in Excel, especially if your zip code appears as a simple number string or as the format "12345-6789", formulas provide a simple solution.
Explanation: The LEFT formula extracts the first 5 digits of a cell's value. This works whether the zip code appears as "12345-6789" or as a simple 9-digit string, as long as the first 5 digits are what you need.
Applicable scenarios: This method is ideal for cases where all zip codes start with the correct base and you do not need to validate their format.
Enter the following formula into a blank cell (e.g., B2):
After confirming with Enter, drag the fill handle down to fill the formula for the rest of your list. Each cell will now display only the first 5 digits of the corresponding zip code.
Tip: If your zip codes may have spaces or different delimiters, or are not all formatted consistently, check for non-standard formatting first and consider using TRIM or SUBSTITUTE functions to clean the data before extracting.
Note: If you encounter an error or unexpected result, check if the original cell contains fewer than 5 digits, extra spaces, or if it is formatted as a number instead of text. These issues could affect the formula output.
Change 9-digit zip codes to 5-digit zip codes with a useful feature
If you have Kutools for Excel, its Remove by Position feature makes shortening 9-digit zip codes to 5 digits extremely straightforward. This approach is practical for quickly handling large lists without entering formulas or worrying about cell formats.
Tips: To apply this Remove by Position feature, first download and install Kutools for Excel. Then you can utilize the feature quickly and easily.
After installing Kutools for Excel, proceed as follows:
1. Format your zip code cells as Text: select your list, then go to Home and choose Text from the Number Format dropdown menu. This step helps prevent Excel from stripping leading zeros.
2. Click Kutools > Text > Remove by Position:
3. In the Remove by Position dialog, enter 5 in the Numbers box, select From right under Position (removing 5 characters from the right end leaves the first 5 digits), then click Ok. Your zip codes will be trimmed to 5 digits instantly:
 |
 |
 |
Tip: Always confirm that your zip codes are uniform in length and do not contain unexpected characters or separators. If your zip codes are mixed formats, double-check the effects of batch operations.
Click to Download Kutools for Excel and a free trial Now!
Change 9-digit zip codes to 5-digit zip codes using Text to Columns
If your zip codes are in the standard format with a hyphen, such as "12345-6789", Excel's Text to Columns feature provides a fast and precise way to separate them. This method splits your 9-digit code into two columns, allowing you to retain only the first 5 digits.
Applicable scenarios: Best suited for lists where zip codes consistently use a hyphen as a separator, and you want to keep the main code for shipping or records.
1. Select the column containing your 9-digit zip codes.
2. Go to the Data tab and click Text to Columns. This will open a setup wizard.
3. Choose Delimited and click Next.
4. Under Delimiters, check the box for Other and type a hyphen - as your delimiter. Then click Next.
5. Specify the destination cell if needed, then click Finish. Your zip codes will be split so that only the first five digits remain in the first column.
Tip: After splitting, you can delete or ignore the secondary column containing the extended part of the zip code.
Note: For zip codes not using a hyphen or formatted differently, this method may not apply. Make sure your data is standardized, or consider using one of the other approaches in this article.
Change 9-digit zip codes to 5-digit zip codes with a VBA Macro
For automated bulk processing, especially with large spreadsheets or recurring tasks, you can employ a VBA macro to trim all zip codes in a selected range to the initial 5 digits. This is valuable if your data is inconsistent, includes leading zeros, or if you want batch conversion without formulas or manual steps.
Applicable scenarios: Ideal for advanced users or regularly processing lengthy lists with varying formats. You only need to select the relevant range, and the macro can enforce the desired zip code length.
1. Open Excel, then click Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, select Insert > Module.
2. Paste the following code into the module area:
Sub TrimZipCodesToFiveDigits()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
Dim zipValue As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select Zip Code Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
zipValue = Rng.Value
If Len(zipValue) >= 5 Then
Rng.Value = Left(zipValue, 5)
End If
Next
End Sub3. In the VBA window, click the 
Run button to execute the code. A dialog appears for you to select your range of zip codes. After confirming, all zip codes in your selected cells will be shortened to just the first 5 digits.
Tips & Precautions: Before running the macro, back up your data to avoid accidental loss. If zip codes include special characters or are formatted inconsistently, confirm the output matches your expectations.
Troubleshooting: If the macro produces errors, verify that you don't have protected sheets, merged cells, or non-numeric values in the target range. You can adjust the code to handle such cases or clean up your data beforehand.
More relative Zip code articles:
- Extract State, Zip Code Or City From Address In Excel
- For extracting state, zip code or city from address cell to individual cells, what would you do? With this article, we will show you formulas to deal with this problem. Please browse for more details.
- Sort Mixed Zip Codes (5 Digits And9 Digits) Ascending In Excel
- As we know, there are two types of zip codes, one is5-digits series, and the other is the9-digits series. In this case, there is a list mixed with these two types of zip codes, the job is to sort the zip codes by first five basic digits as below screenshot shown, how can you quickly handle it?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
