How to Remove Last Digit in Excel [6 Methods]

The elimination of the last digit from a certain cell or the entire column is a common task in Microsoft Excel. It is particularly significant when the unnecessary character is added at the end of desired numbers (such as street number, phone number, registration ID, etc.), then there arises the need to remove the last digit. Fortunately, Excel has a variety of features to accomplish this. Hence, in this article, I will show you 6 different methods to remove the last digit in Excel.

In the following dataset, the Initial ID columns contain some messy student IDs. In the Final ID column, we will use formulas and VBA to show you how to remove the unwanted last characters.

Remove Last Digit in Excel Using LEFT & LEN Functions

The easiest way to remove the last digit in Excel is by using the LEFT & LEN functions. The LEFT function removes a specified number of characters from the left side of a string. Now follow the steps below to see how it works.

Syntax

=LEFT(text, LEN(text)-1)

Formula

=LEFT(B2, LEN(B2)-1)

Formula Explanation

• LEFT: This is an Excel text function that extracts a specified number of characters from the beginning (left side) of a text string.
• B2: This refers to the cell B2 in the worksheet. It’s the cell from which you want to extract a portion of text.
• LEN(B2): This part of the formula calculates the length (number of characters) of the text in cell B2. The LEN function returns the size of a text string.
• LEN(B2) – 1: This subtracts 1 from the length of the text in cell B2. The purpose of subtracting 1 is to remove the last character from the text string.

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

1. Select cell C2.
2. Apply the formula: =LEFT(B2, LEN(B2)-1)
3. Press the ENTER button.
4. Drag the Fill Handle from cell C2 to C10.

So, the last digit has been removed successfully. See the screenshot:

Remove the Last Digit in Excel Using the REPLACE & LEN Functions

Another approach to removing the last digit in Excel is by utilizing the REPLACE & LEN functions. The REPLACE function replaces a specified portion of a text string with another text string. The procedures of this method are given below.

Syntax

=REPLACE(old_text, LEN(old_text), num_chars, new_text)

Formula

=REPLACE(B2,LEN(B2),1,"")

Formula Explanation

• REPLACE: This is an Excel function used for replacing characters within a text string.
• B2: This refers to cell B2 in the worksheet. It’s the cell from which you want to modify the text.
• LEN(B2): The LEN function calculates the length (number of characters) of the text in cell B2. In this context, it determines the position of the last character in the text.
• 1: This number specifies the number of characters you want to replace. In this case, it’s set to 1, indicating that you want to replace only the last character. (If you want to replace more than one digit, you can edit this number according to yours. )
• “”: This empty double quotation mark effectively means “replace with nothing.” It instructs Excel to remove the character(s) specified in the previous step.

To remove the last digit in Excel using the REPLACE & LEN functions:

1. Select cell C2.
2. Apply the formula: =REPLACE(B2,LEN(B2),1,””)
3. Press the ENTER button.
4. Double-click on the Fill Handle.

By using the REPLACE & LEN functions, we have removed the last digit. See the image below:

Remove the Last Digit in Excel with Flash Fill

If your dataset has a fixed pattern, you can take the leverage of the Flash Fill tool. Once it recognizes the pattern, it will automatically remove the last digit within a blink of an eye. But the Flash Fill feature is available only on Microsoft Excel 2013 and the later versions.

Follow these steps to remove the last digit in Excel using Flash Fill:

1. Insert the first value in a cell without the last digit.
2. Select the range you want to fill.
3. Go to Data.
4. Select Flash Fill from the Data Tools group. Or, click CTRL+E.

After applying Flash Fill, you will see the result in the Final ID column. We removed the last digit from the Initial ID column.

Delete the Last Digit from the Numerical String Using the TRUNC Function

If your dataset contains the numerical value, you can follow an alternate way to remove the last digit, TRUNC functions. You need to pursue some processes.

Syntax

=TRUNC(number,[num_digits])

Formula

=TRUNC(B2/10)

Formula Explanation

• B2/10: This divides the value of cell B2 by 10 which puts the last digit after the decimal point. So ID number 1521221 becomes 152122.1.
• TRUNC(): This Excel function is used to truncate or remove the fractional portion of a number. So, 1521221 becomes 152122. Thus, the last digit is removed.

To remove the last digit from the numerical string using the TRUNC function:

1. Select cell C2.
2. Type this formula: =TRUNC(B2/10)
3. Press the ENTER button.
4. Now drag the Fill Handle from cell C2 to C10 or double-click on Fill Handle.

Here in the initial ID column, there is an undesired number at the end. We have achieved a modified column in Final ID after applying the TRUNC function.

Clear the Last Digit in Excel with VBA Subroutine

To remove the last digit in Excel using VBA, follow these steps:

1. Right-click on the sheet name and choose View Code to open Visual Basic Editor.
2. Go to Insert > Module.
3. Copy the code below and paste it.

   Sub RemoveLastCharacter()
   
   Dim rng As Range
   Dim cell As Range
   Dim newValue As String
   
   ' Prompt the user to select a range
   On Error Resume Next
   Set rng = Application.InputBox("Select a range of cells:", Type:=8)
   On Error GoTo 0
   
   ' Check if a range was selected
   If rng Is Nothing Then
   MsgBox "No range selected. Operation canceled.", vbExclamation
   
   Exit Sub
   End If
   
   ' Loop through each cell in the selected range
   For Each cell In rng
   If Len(cell.Value) > 0 Then
   ' Check if the cell is not empty
   newValue = Left(cell.Value, Len(cell.Value) - 1)
   cell.Value = newValue
   End If
   Next cell
   
   MsgBox "Last character removed from the selected range.", vbInformation
   
   End Sub

   

4. Now, move to the worksheet and select the Developer tab. Then, Click on the Macros. Alternatively, you can press ALT+F8 to open the Macro dialog box.
5. Click on Run.
6. You will have to select a range then.

After clicking on the OK button on the Input dialog box, the last digits of the selected cells will disappear.

Creating User-Defined Function with VBA to Remove Last Digit in Excel

Another approach is to build a user-defined function using VBA to remove the last digit in Excel. Here are the steps to follow:

1. Right-click on the sheet name and select View Code to open Visual Basic Editor.
2. Go to Insert > Module.
3. Copy the given code and paste it.

   Function ClearLastDigit(inputValue As String) As String
   
   If Len(inputValue) > 0 Then
   ClearLastDigit = Left(inputValue, Len(inputValue) - 1)
   Else
   ClearLastDigit = inputValue
   End If
   
   End Function

   
4. Save the code and go to the Excel worksheet.
5. Click on cell C2 and apply this formula: =ClearLastDigit(B2)
6. Click on Fill Handle to copy the formula from C2 to C10.

Here we created a user-defined function using VBA. Finally, we have removed the last digit successfully after applying it. See the image.

Conclusion

In this article, I’ve discussed 6 methods to remove the last digit from your dataset. Whether you prefer using built-in functions or creating custom VBA macros, these techniques allow you to manipulate your data effectively and streamline your analysis tasks. By mastering these methods, you can become more proficient in Excel and enhance your data processing skills.

Frequently Asked Questions

How do I get rid of the last 3 digits in Excel?

To remove the last 3 digits in Excel:

1. In an empty cell, use the formula: =LEFT(A1, LEN(A1)-3)
2. Press ENTER.
3. Copy the result.
4. Right-click on the cell with the original text.
5. Select Paste Special.
6. Choose Values and click OK.

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.