# 7 Ways to Remove Last 3 Digits in Excel

One common requirement that arises during data processing is the need to remove the last three digits from numbers or text. Whether you are dealing with large datasets or a few cells, Excel provides multiple approaches to accomplishing this task efficiently. In this article, I will explore 7 different ways to remove the last 3 digits in Excel.

### Introduction to the Dataset

ID Number: This column contains some ID numbers of a product.

Final ID Number: It contains the IDs after removing the last 3 digits from them. ## Easiest Way to Remove Last 3 Digits in Excel

A simple way to remove the last 3 digits in Excel is by using the LEFT and LEN functions. The LEFT function is used to extract a specified number of characters from the beginning of a text string. The LEN function calculates the length of a text string.

Syntax

`=LEFT(string, LEN(string)-number_of_chars_to_remove)`

Formula

`=LEFT(A2, LEN(A2)-3)`

Usage Guide

Step_1: First, copy the formula from above.

Step_2: Secondly, paste it into cell B2.

Step_3: Press ENTER to insert the formula. Formula Explanation

• LEFT: This is an Excel function that extracts a specified number of characters from the left (beginning) of a text string.
• A2: This is the cell reference to the text string from which we want to extract characters. In this case, it refers to cell A2.
• LEN(A2): The LEN function is used to determine the length of the text string in cell A2, including spaces and characters.
• LEN(A2) – 3: By subtracting 3 from the length of the text string, we are effectively excluding the last three characters from the extracted result.

Step_4: Now drag the Fill Handle to copy the formula to the rest of the cells. Final Result >

Thus, using this formula, you can get the filtered dataset where the last 3 digits are removed. See the Final Result: ### Alternative Method #1: Use the MID & LEN Functions to Remove the Last 3 Digits in Excel

The MID function in Excel is used to extract a specific substring from a text string. It allows you to specify the starting position and the number of characters you want to extract. By using the MID function combined with the LEN function, you can remove the last 3 digits in Excel.

Syntax

`=MID(text, start_num, LEN(text)-3)`

Formula

`=MID(A2,1, LEN(A2) - 3)`

Usage Guide

Step_1: First, copy the formula from above.

Step_2: Secondly, paste it into cell B2.

Step_3: Press ENTER to insert the formula. Formula Explanation

• MID: This function in Excel is used to extract a specified number of characters from within a text string. It requires three arguments: the text string, the starting position, and the number of characters to extract.
• A2: This is the cell reference that contains the text from which you want to extract a substring.
• LEN(A2) – 3: This part calculates the length of the text in cell A2 using the LEN function and then subtracts 3 from it. This value is used as the starting position for the MID.

Step_4: Next drag the Fill Handle to copy the formula to the rest of the cells.

Final Result >

This is an alternative way to remove the last 3 digits from a text string. ### Alternative Method #2: Clear the Last 3 Digits Using the REPLACE Function

The REPLACE function in Excel is used to replace a specified number of characters within a text string with new characters. It’s commonly used for manipulating and modifying text strings within cells. By using the REPLACE function with the LEN function, you can also remove the last 3 digits in Excel.

Syntax

`=REPLACE(old_text,LEN(old_text)-3)`

Formula

`=REPLACE(A2, LEN(A2)-2, 3, "")`

Usage Guide

Step_1: First, copy the formula from above.

Step_2: Secondly, paste it into cell B2.

Step_3: Press ENTER to insert the formula. Formula Explanation

• REPLACE: This is an Excel function that allows you to replace a portion of text within a given text string.
• A2: This is the cell reference that contains the original text.
• LEN(A2) – 2: This part calculates the starting position for the replacement. It subtracts 2 from the length of the text in cell A2. This effectively positions the replacement starting from the third-to-last character of the text.
• 3: This specifies the number of characters to replace, starting from the position calculated above.
• “”: This is the replacement text, in this case, an empty string. This means that the specified portion of the text will be removed from the original text.

Step_4: Drag the Fill Handle to copy the formula to the rest of the cells.

Final Result >

This is how using this formula, you can get the filtered dataset where the last 3 digits are removed by the REPLACE function. ### Alternative Method #3: Use SUBSTITUTE Function to Remove the Last 3 Digits in Excel

In Excel, the SUBSTITUTE function is used to replace occurrences of a specific substring within a text with another substring. By using it with the RIGHT function, you can remove the last 3 digits using Excel.

Syntax

`=SUBSTITUTE(text, old_text, new_text, [instance_num]`

Formula

`=SUBSTITUTE(A2, RIGHT(A2, 3), "")`

Usage Guide

Step_1: First, copy the formula from above.

Step_2: Secondly, paste it into cell B2.

Step_3: Press ENTER to insert the formula. Formula Explanation

The SUBSTITUTE function in Excel is used to substitute one text string with another within a given text string. It requires three arguments: the original text, the text you want to replace, and the text you want to replace it with.

• A2: This is the cell reference that contains the original text.
• RIGHT(A2, 3): This part extracts the rightmost 3 characters from the text in cell A2 using the RIGHT This is the portion of text that you want to replace.
• “”: This is the replacement text, in this case, an empty string. This means that the extracted portion of the text will be removed from the original text.

Step_4: Drag the Fill Handle to copy the formula to the rest of the cells.

Final Result >

Now, using this formula, you can get the filtered dataset where the last 3 digits are removed. ### Alternative Method #4: Use Flash Fill to Remove the Last 3 Digits in Excel

If your dataset contains a specific pattern, you can use the Flash Fill feature to remove the last 3 digits without any formula.

Note: It is available only on Microsoft Excel 2013 and later versions.

For example, this dataset has a pattern and a space before every last 3 digits.

Usage Guide

Step_1: At the top cell, cell B2 of the Final ID Number column, manually write the ID Number without the last 3 digits.

Step_2: Press the ENTER key on the keyboard. Step_3: At the top of the Excel Menu bar, find Fill, and then click Flash Fill. Tip: You can also find Flash Fill on the Data tab> Data Tools group> Flash Fill. Final Result >

Using the Flash Fill, you can get the filtered dataset where the last 3 digits are removed. See Screenshot: ### Alternative Method #5: Use VBA Code to Remove the Last 3 Digits in Excel

VBA is the short form of Visual Basic for Applications. By creating a custom VBA user-defined function, you can instantly remove the last 3 from a given dataset.

Usage Guide

Step_1: Right-click on the worksheet name (VBA for my example) and choose View Code. A Visual Basic Editor will open. Step_2: Go to Insert > Module. Step_3: Copy the following code:

``````Function RemoveLastThreeDigits(inputText As String) As String

If Len(inputText) <= 3 Then
RemoveLastThreeDigits = ""
Else
RemoveLastThreeDigits = Left(inputText, Len(inputText) - 3)
End If

End Function``````

Step_4: Press CTRL + V to paste the code into the blank module that I’ve just created. Step_5: Press on the keyboard CTRL + S to save the file.

A dialog box will pop up.

Step_6: Click No.

Step_7: Save your Excel file as an Excel Macro-Enabled Workbook. Now let’s remove the last 3 digits using the function RemoveLastThreeDigits(A2).

Usage Guide

Step_1: Write this formula in cell B2.

Step_2: Press ENTER to insert the formula. Step_3: Drag the Fill Handle to copy the formula to the rest of the cells.

Final Result >

In this way, you can get the filtered dataset where the last 3 digits are removed. ### Alternative Method #6: Use the TRUNC Function to Remove the Last 3 Digits in Excel

The TRUNC function in Excel is used to truncate a number by removing the decimal portion. It takes two arguments: the number you want to truncate and an optional number of digits to preserve after truncating.

Then, simply cut off the decimal portion of the number, leaving only the integer part or the specified number of decimal places.

Here is the dataset that I am going to use for this example of demonstrating the TRUNC function: Syntax

`=TRUNC(number, [num_digits])`

Formula

`=TRUNC(A1/1000,0)*1000`

Usage Guide >

Step_1: First, copy the formula from above.

Step_2: Secondly, paste it into cell B2.

Step_3: Press ENTER to insert the formula. Formula Explanation

• A2 refers to the cell containing the number from which you want to remove the last three digits.
• A2/1000 divides the number in cell A2 by 1000. Dividing by 1000 effectively removes the last three digits from the number.
• TRUNC(A2/1000, 0) is then used to truncate the result of the division to 0 decimal places.

Step_4: Drag the Fill Handle to copy the formula to the rest of the cells.

Final Result > Thus, using this formula, you can get the filtered dataset where the last 3 digits are removed.

## Conclusion

In conclusion, Microsoft Excel provides multiple methods to remove the last 3 digits from text and numeric data. Depending on the type of data and your specific requirements, you can choose the method that suits you best.