Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


10 Suitable Ways to Remove Last 4 Digits of Zip Code in Excel

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.

Remove Last 4 Digit of Zip Code in Excel


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

Remove last 4 digit of Zip codes using LEFT Function

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

Remove Last 4 digit in Excel using LEFT Function

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

Remove last 4 digit of Zip Codes using LEFT FUnction

Read More: How to Format Zip Code to 5 Digits in Excel (5 Easy Methods)


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

In this method, we will use the MID function to get the desired result. 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.

How to remove last 4 digit of Zip codes using MID Function

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

How to remove last 4 digit of Zip codes using MID Function

  • 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: How to Create Zip Code with Excel Formula (6 Easy Ways)


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.

How to remove last 4 digit of Zip codes using Text to Column tool

  • Next, go to the Data.

How to remove last 4 digit of Zip codes using Text to Column tool

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

How to remove last 4 digit of Zip codes using Text to Column tool

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

How to remove last 4 digit of Zip codes using Text to Column tool

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

How to remove last 4 digit of Zip codes using Text to Column tool

Read More: How to Format Zip Codes in Excel (2 Easy Methods)


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

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.

How to remove last 4 digit of Zip codes using INT Function

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

How to remove last 4 digit of Zip codes using INT Function

Read More: How to Concatenate ZIP Codes in Excel (3 Easy Methods)


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, “-”, ”.”)

How to remove last 4 digit of Zip codes using INT and SUBSTITUTE Function

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


Similar Readings


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)

How to remove last 4 digit of Zip codes using TEXT and LEFT Functions

  • 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 Last 4 Digits of 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.

How to remove last 4 digit of Zip codes using a VBA Code

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

How to remove last 4 digit of Zip codes using a VBA Code

  • Copy the following code in the window.
Sub ZIPShorter () 
For Each cell In Selection cell.Value = Left(cell.Value, 5)
Next 
End Sub
How to remove last 4 digit of Zip codes using a VBA Code
  • To run the code, press alt+f8 and click on Run on the pop-up box.

How to remove last 4 digit of Zip codes using a VBA Code

  • 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)

How to remove last 4 digit of Zip codes using SUM, LEN, SUBSTITUTE Function

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 Last 4 Digits of 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)

How to remove last 4 digit of Zip codes using LEFT, MIN & FIND Functions

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


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)

How to remove last 4 digit of Zip codes using ISNUMBER, RIGHT, LEFT & LEN Functions

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 the cell D5.

● 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 the cell D5.


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.

Concluding Remarks

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.


Related Articles

Alif Bin Hussain

Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo