How to change 9-digit zip codes to 5 digits in Excel?
Normally, we use the 5-digit zip code in the US, But, sometimes, you may have a list of 9-digit zip codes, how could you convert the 9-digit zip codes to 5-digit short zip codes in Excel?
Change 9-digit zip codes to 5-digit zip codes with 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 with formula
To only keep the first 5 digits from the 9-digit zip code, you can apply the below simple formula, please do as this:
Enter the following formula into a blank cell:
And then, drag the fill handle down to the cells that you want to apply this formula, and then, all the first 5 digits have been extracted, see screenshot:
Change 9-digit zip codes to 5-digit zip codes with a useful feature
If you have Kutools for Excel, with its Remove by Position feature, you can also finish this job quickly.
Tips:To apply this Remove by Position feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.
After installing Kutools for Excel, please do as this:
1. Firstly, you should format your zip code cells as Text format, please select the list of zip codes, and then click Home, and then select Text from the General drop down list, see screenshot:
2. Then, click Kutools > Text > Remove by Position, see screenshot:
3. In the Remove by Position dialog box, enter 5 into the Numbers text box, and then select From right option under the Position section (It means remove 5 characters from the right side of the cell), and then, click Ok button, and you will get the result as you need:
 |
 |
 |
Click to Download Kutools for Excel and free trial Now!
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 And 9 Digits) Ascending In Excel
- As we know, there are two types of zip codes, one is 5-digits series, and the other is the 9-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!
