This is the sample dataset.

### Method 1 – Applying the LEFT Function to Remove the Last 4 Digits of a Zip Code

**Steps:**

- Click
**D5**. - Enter the following formula.

**C5 **refers to the cell with the 9-digit Zip code. “**5**” refers to the number of digits you 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, it returns the first 5 digits in **C5**.

- Press
**Enter.**

The last 4 digits of the Zip code are removed.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

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

### Method 2 – Use the MID Function to Cut the Last 4 Digits of the Zip Code

**Steps:**

- Click
**D5**. - Enter the following formula.

`=MID(C5, 1, 5)`

**C5 **refers to the cell containing the Zip code. To keep the first 5 digits, “**1**” and “**5**” are used. The **MID** function returns the digits from the first to the fifth digit.

.

- Press
**Enter**.

**D5 **will show the first 5 digits of the Zip code.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

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

### Method 3 – Use the Text to Columns Feature

**Steps:**

**Select**the column containing the Zip codes.

- Go to
**Data.**

- Click
**Text to Columns**. - Select
**Delimited**and click**Next**.

- Enter “
**–**” in**Other**to separate the digits. - Click
**Next**.

- Enter the
**Destination**or click the small**arrow**and select the cell. - Click
**Finish**.

This is the output.

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

### Method 4 – Applying the INT Function to Remove the Last 4 Digits of the Zip Code in Excel

**Steps:**

- Click
**D5**. - Enter the following formula.

`=INT(C5/10000)`

**C5 **is divided by 10000 to remove the last 4 digits. The **INT Function **returns the integer value.

- Press
**Enter**.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

**Read More:** How to Concatenate ZIP Codes in Excel

### Method 5 – Combine the INT and the SUBSTITUTE Functions

**Steps:**

- Replace “
**–**” with”**.**”. - Click
**D5**. - Enter the following formula.

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

The following formula will return an integer value: the 5 digit Zip code.

`=INT(D5)`

- You can get the result directly by combining these formulas:

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

- Press
**Enter**. - Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 6 – Merging the TEXT and the LEFT Functions

**Steps:**

- Click
**D5**. - Enter the following formula.

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

- Press
**Enter**. - Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 7 – Running a VBA Code to Remove the Last 4 Digits of the Zip Code in Excel

**Steps:**

**Select**the column containing the Zip codes.

**Right-click**the**Sheet**(it was named VBA Code).- Select
**View Code**.

- Copy the following code into 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**Run**.

This is the output.

### Method 8 – Combine the SUM, LEN, and SUBSTITUTE Functions

**Steps:**

- Click
**D5**. - Enter 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**counts the number of digits in**C5**. If the total number of digits is more than 5, it will proceed to the next operation.**LEFT(C5,LEN(C5)-5)**cuts the last 4 digits in**C5**.- If the number in
**C5**is not more than 5 digits, the final formula returns the**C5**value.

- Press
**Enter.** - Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Method 9 – Merging the LEFT, MIN, and FIND Functions to Remove the Last 4 Digits of the Zip Code in Excel

**Steps:**

- Click
**D5**. - Enter the following formula.

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

**Formula Breakdown**

- The
**FIND**function provides 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**. - The
**MIN**function returns the smallest numbers extracted by the**FIND**function**(FIND({0,1,2,3,4,5,6,7,8,9},C5&”0123456789″)** - The
**LEFT**function returns the specified number of characters from the start of the string. Here, the last 4 digits.

- Press
**Enter**. - Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

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

### Method 10 – Combining the ISNUMBER, RIGHT, LEFT, and LEN Functions

**Steps:**

- Click
**D5**. - Enter the following formula.

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

**Formula Breakdown**

- The
**RIGHT**Function returns the specific number of characters from the end of the string. Its reference cell number is C5, and num_chars is 8. The formula becomes,**RIGHT(C5,8)*1** - The
**ISNUMBER**Function verifies if the result found by**RIGHT(C5,8)**is a number. If so, it shows the result of this formula**LEFT(C5,LEN(C5)-4)**which cuts the last 4 digits from the zip code. If not, the formula returns the value in**C5.**

- Press
**Enter**.

The last 4 digits of the Zip code are removed.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

**Read More:** How to Format Zip Codes in Excel

**Download Practice Workbook**

Download the practice workbook.

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