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.

**Table of Contents**Expand

## 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.

`=LEFT(C5,5)`

**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.

`=MID(C5, 1, 5)`

- 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.

`=INT(C5/10000)`

**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.

`=SUBSTITUTE(C5, “-”, ”.”)`

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

`=INT(D5)`

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

`=INT(SUBSTITUTE(C5, “-”, ”.”)`

- 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.

`=LEFT(TEXT(C5,"00000"),5)`

- 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.

`=IF(SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},)))>5,LEFT(C5,LEN(C5)-5),C5)`

**Formula Breakdown**

**SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{1,2,3,4,5,6,7,8,9,0},)))>5**will count the number of digits in the cell**C5**. If the total number of digits is more than 5, it will proceed to the next operation.**LEFT(C5,LEN(C5)-5)**is used to cut the last 4 digits of the zip code from the reference cell**C5**.- Now if the number in cell
**C5**is not more than 5 digits, the final formula then will return**C5**value.

- 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.

`=LEFT(C5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C5&"0123456789"))+4)`

**Formula Breakdown**

- The
**FIND**function will provide the starting position of a string within a string. So its find_text argument is**{0,1,2,3,4,5,6,7,8,9},**and it will find the string within**C5**cell numbers. - The
**MIN**function then returns the smallest numbers extracted from the**FIND**function**(FIND({0,1,2,3,4,5,6,7,8,9},C5&”0123456789″)** - The
**LEFT**function will now give us the specified number of characters from the start of the string. In this case, the number is the last 4 digits.

- 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.

`=IF(ISNUMBER(RIGHT(C5,8)*1),LEFT(C5,LEN(C5)-4),C5)`

**Formula Breakdown**

- The
**RIGHT**Function here will give you the specific number of characters from the end of the number string. Its reference cell number is C5, and num_chars is 8. So the formula becomes,**RIGHT(C5,8)*1** - The
**ISNUMBER**Function will verify if the result found from**RIGHT(C5,8)**is a number or not. If so, it will show the result of this formula**LEFT(C5,LEN(C5)-4)**which cut the last 4 digits from the zip code. If not, the formula will return**C5.**

- 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**