How to Remove the Last 4 Digits of a Zip Code in Excel

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.

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.

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.

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.

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.

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.