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.

The dataset I used for this article consists of two columns. First, the ID Number column contains some ID numbers of a product. Then, I inserted an empty column named Final ID number. So, I will store the IDs after removing the last 3 digits from them.

Remove Last 3 Digits in Excel Using LEFT & LEN Functions

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)

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.

To remove the last 3 digits in Excel using LEFT & LEN functions, follow these steps:

1. Select cell B2.
2. Apply the formula: =LEFT(A2, LEN(A2)-3)
3. Press ENTER to insert the formula.
4. Now drag the Fill Handle from cell B2 to B10.

Thus, using this formula, you can get the filtered dataset where the last 3 digits are removed. See the final result:

Remove the Last 3 Digits in Excel Using the MID & LEN Functions

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)

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.

To remove the last 3 digits in Excel using the MID & LEN functions:

1. Select cell B2.
2. Apply the formula: =MID(A2,1, LEN(A2) – 3)
3. Press ENTER to apply the formula.
4. Double-click on the Fill Handle.

This is an alternative way to remove the last 3 digits from a text string.

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, "")

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.

Follow these steps to remove the last 3 digits in Excel using the Replace function:

1. Select cell B2.
2. Write this formula: =REPLACE(A2, LEN(A2)-2, 3, “”)
3. Press the ENTER key to apply the formula. 
4. Drag the Fill Handle to copy the formula to the rest of the cells.

This is how using this formula, you can get the filtered dataset where the last 3 digits are removed by the REPLACE function.

Use the 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), "")

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.

Follow these steps to remove the last 3 digits in Excel using the SUBSTITUTE function:

1. Select cell B2.
2. Apply the formula: =SUBSTITUTE(A2, RIGHT(A2, 3), “”)
3. Press the ENTER button.
4. Double-click on the Fill Handle.

Now, using this formula, you can get the filtered dataset where the last 3 digits are removed.

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. For example, this dataset has a pattern and a space before every last 3 digits.

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

Follow the below steps to remove the last 3 digits with Flash Fill in Excel:

1. Manually type the cell content of cell B2 without the last 3 digits.
2. Select the range you want to fill.
3. Go to the Home tab.
4. Go to Editing group > Fill drop-down menu > Flash Fill command.

Shortcut key: press CTRL+E.

Alternatively, you can go to the Data tab > Data Tools group > Flash Fill tool to find Flash Fill command.

Using the Flash Fill command, you can get the filtered dataset where the last 3 digits are removed. See screenshot:

Remove the Last 3 Digits in Excel Using the VBA Code

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.

Follow the guide to apply VBA code to remove the last 3 digits:

1. Right-click on the worksheet name VBA and select View Code to open Visual Basic Editor.
2. Go to Insert > Module.
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

4. Press CTRL + V to paste the code into the blank module.
5. Press on the keyboard CTRL + S to save the file and click No in the popped-up dialog box.
6. Save your Excel file as an Excel Macro-Enabled Workbook.
7. Then, type this formula in cell B2: =RemoveLastThreeDigits(A2).
8. Press ENTER to apply the formula.
9. Drag the Fill Handle to copy the formula to the rest of the cells.

In this way, you can get the filtered dataset where the last 3 digits are removed.

Apply 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

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.

Steps to remove the last 3 digits with the TRUNC function:

1. Type this formula in cell B2:=TRUNC(A1/1000,0)*1000
2. Press ENTER to apply the formula.
3. Drag the Fill Handle to copy the formula to the rest of the cells.

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.

Frequently Asked Questions

How do I get rid of the last digit in Excel?

To remove the last digit in Excel:

1. Select a cell B1.
2. Use the formula: =LEFT(A1, LEN(A1)-1)
3. Press ENTER.
4. Copy the result from cell B1.
5. Double-click on the Fill Handle of cell B1.

How do I separate the last 4 digits in Excel?

To separate the last 4 digits in Excel:

1. Select cell A2.
2. Use the formula: =RIGHT(A1, 4)
3. Press ENTER.

The cell containing the formula will now display the last 4 digits from the text in cell A1.