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?
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:
If you have Kutools for Excel, with its Remove by Position feature, you can also finish this job quickly.
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:
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?