How to Remove Last 4 Digits of Zip Code in Excel (10 Easy Ways)

Zip codes are essential for delivering parcels through postal services to reach a correct destination. Zip codes contain 9 digits, among which the first 5 digits are enough for most of the uses. Therefore, in this article I will show you 10 unique ways to remove the last 4 digits of Zip code in Excel and get the 5-digit codes.

How to Remove Last 4 Digits of Zip Code in Excel: 10 Easy Ways

In this article, we will learn 10 suitable ways to remove the last 4 digits of Zip code in Excel. For this purpose, we will use the following dataset.

1. Apply LEFT Function to Remove Last 4 Digits of Zip Code

This is the fastest and easiest method to remove the last 4 digits of the Zip codes and get Zip codes with 5 digits. Follow these steps to learn!

Steps:

• First of all, click on the empty cell (D5) beside the column containing the Zip codes.
• Then apply the following formula. C5 refers to the cell with 9-digit Zip code. “5” refers to the number of digits we want to keep from the left.

• The LEFT function returns the first character or characters in a text string, based on the number of characters you specify. Here, the LEFT function will return the first 5 digits from cell C5.

• Now press Enter. It will remove the last 4 digits of the Zip code.

• To get the results for all of the Zip codes, hold on the left button of the mouse at the bottom right corner of the D5 cell and drag it down to cell D14.

• Thus you will get the desired results for all the Zip codes.

Read More: How to Format Zip Code to 5 Digits in Excel

2. Insert MID Function to Cut the Last 4 Digits of the Zip Code

In this method, we will use the MID function to get the desired result. The MID function helps you to keep a specific part of a text or number and remove the rest.

Steps:

• First, type the following formula in the D5 cell.

• Here, C5 refers to the cell containing the Zip code. We want to keep the first 5 digits from the code. That’s why we used “1” as the start number and “5” as the number of characters we want to keep in the results.
• MID function returns a specific number of characters from a text or number. Here, the MID function will return the digits from the first through the fifth.

• Hit Enter. Cell D5 will show you the first 5 digits of the Zip code.

• To use the formula for all the data, double-click at the bottom right corner of the D5 It will remove the last 4 digits of all the Zip codes.

Read More: [Fixed] Zip Codes in Excel Starting with 0

3. Use Text to Columns Feature

Now we will learn how to use the Text to Columns tool. We will separate the first 5 data from the rest using this tool. You can only use this method if there is a hyphen (-) after the fifth digit.

Steps:

• First, select the column containing all the Zip codes.

• Next, go to the Data.

• Now click on the Text to Columns A pop-up will open.
• Select Delimited and click on Next.

• Then type a “–” in the Other box to separate the digits.
• After that, click on Next.

• Next, you will have to type the Destination where you want to keep the results or you can just click on the little arrow and select your desired cell.
• Press Finish to complete the procedure.

• You can now see that all the 5-digit Zip codes are shown in the Destination column.

Read More: How to Sort Data by Zip Code in Excel

4. Apply INT Function to Remove the Last 4 Digits of the Zip Code in Excel

The INT function keeps the integer portion of a value. We will use this function to remove the last 4 digits of Zip codes. This method will only work if there is no hyphen (–) between the numbers.

Steps:

• To begin with, click on the empty cell D5 and type the following formula.

• C5 is the cell with 9-digit Zip codes. It is divided by 10000 (one followed by four zeros) to remove the last 4 digits. INT returns the integer value of a number. Here, the INT function returns the integer value from cell.

• Press Enter and you will get the desired result.

• To copy the formula to all the cells, just double-click at the bottom right corner of cell D5.
• It will get you the desired result for all the Zip codes.

Read More: How to Concatenate ZIP Codes in Excel

5. Combine INT and SUBSTITUTE Functions

Now we will use more than one formula and combine them together to get the results. First, we will use the INT and SUBSTITUTE functions.

Steps:

• First, we need to replace the “–” with”.”. To do that, click on the D5 cell and write down the below formula.

• Then the following formula will return an integer value which is our desired 5 digit Zip code.

• However, we can get the result directly by combining these formulas.

• Hit Enter and double-click at the bottom right corner of cell D5 to remove the last 4 digits for all Zip codes.

6. Merge TEXT and LEFT Functions

In this technique, we will merge the TEXT and LEFT functions to get the 5-digit Zip codes.

Steps:

• Select the D5 cell and type the following formula.

• Press Enter and double-click at the bottom right corner of cell D5 to get the Zip codes with 5 digits.

7. Run a VBA Code to Remove the Last 4 Digits of the Zip Code in Excel

If you want to remove the last 4 digits of Zip codes in Excel without using the formula, you can just run a VBA code.

Steps:

• First of all, select the column containing Zip codes.

• Right-click on the Sheets (we named the Sheet VBA Code) from the bottom of the window.
• Then select View Code. A new window will appear.

• Copy the following code in the window.

Sub ZIPShorter () 
For Each cell In Selection cell.Value = Left(cell.Value, 5)
Next 
End Sub

• To run the code, press alt+f8 and click on Run on the pop-up box.

• After the run is complete, you will get the results.

8. Combine SUM, LEN, and SUBSTITUTE Functions

Now we will combine three functions for the same purpose as the above. We will use SUM, LEN, and SUBSTITUTE functions in this method.

Steps:

• Select the D5 cell and write down the following formula.

• Hit Enter and double-click at the bottom right corner of cell D5 to get the desired output.

9. Merge LEFT, MIN, and FIND Functions to Remove the Last 4 Digits of the Zip Code in Excel

In this method, we will merge 3 functions (LEFT, MIN, and FIND) to eliminate the last 4 digits of the Zip codes.

Steps:

• Left-click on the D5 cell and type the formula given below.

• Press Enter on the keyboard and cell D5 will show the result.
• To get the results for all the codes, double-click the D5 cell at the bottom right corner and you will get your desired results.

Read More: How to Find ZIP Code from Address in Excel

10. Combine ISNUMBER, RIGHT, LEFT, and LEN Functions

This time we will combine ISNUMBER, RIGHT, LEFT, and LEN Functions together to separate the first 5 digits from the 9-digit Zip codes.

Steps:

• At the beginning, use your mouse to select the empty cell
• Now write down the below formula.

• Hit Enter. It will eliminate the last 4 digits of the code.
• Finally, Get the results for all the Zip codes by double-clicking the bottom right corner of cell D5.

Read More: How to Format Zip Codes in Excel

Things to Remember

• While using a formula, don’t forget to give proper cell references or you won’t get the desired results.
• The INT function won’t work if there is a hyphen (-) between the numbers.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

Thank you for reading this article. I hope you find it useful. Now you know 10 different ways of removing the last 4 digits of Zip codes. Please share any queries you have and give us your valuable suggestions in the comment section below.

<< Go Back to Zip Code in Excel | Number Format | Learn Excel