How to Remove Last Digit in Excel (6 Quick Methods)

num_digit- This argument is optional. This argument indicates how many digits of the fraction will remain in the return. If this part is blank or 0, no fraction will be shown on the return.

Now, we will show how this function is applied to remove the last digit.

Step 1:

First, go to Cell C5.
Write the below formula on that cell.

=TRUNC(B5/10)

Step 2:

Now, press the Enter button.

We can see that the last digit is removed from the data of Cell B5.

Step 3:

Now, drag the Fill handle icon towards the last cell.

So, the last digits are pulled from the data of Column B. We split all the values by “10” and removed all the fractional values.

2. Insert LEFT Function with LEN Function to Remove Last Digit

The LEFT function provides the characters or digits from the starting or the left side of a series.

Syntax:

LEFT(text,[num_chars])

Argument:

text- This is the reference series from which we will get the required number of digits or characters.

num_chars- This argument is optional. It defines how many digits we want from the given series. It must be equal to or greater than 0.

The LEN function returns the length of a series.

Syntax:

LEN(text)

Argument:

text- This is the given series or string whose length will be calculated by this function.

We will insert the LEFT function with the LEN function.

Step 1:

First, go to Cell C5.
Then write the following formula on that cell.

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

Step 2:

Now, press Enter.

Step 3:

Now, drag the Fill handle icon to the last cell.

We can see that the last digit of each cell of Column B is eliminated.

Similar Readings

3. Combine REPLACE & LEN Functions to Remove Last Digit

The REPLACE function replaces several digits or characters from a series based on your choice.

Syntax:

REPLACE(old_text, start_num, num_chars, new_text)

Argument:

old_text- This is the given series where replace will occur.

start_num-This defines the location of the old_text from where replace will start.

num_chars- This indicates how many digits will be replaced.

new_text- Those are the digits that will be set on the old_text.

We will combine the REPLACE and LEN functions in this method.

Step 1:

Put the following formula in Cell C5.

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

Step 2:

Click on the Enter button.

Step 3:

Drag the Fill Handle icon towards the last cell.

This combination easily removed the last digit of given numbers.

Read More: How to Remove Parentheses in Excel

4. Withdraw Last Number Using Excel Flash Fill

Excel Flash Fill automatically fills a column based on a clue. We can make a pattern of data manipulation. And that can easily be applied by this Flash Fill.

This is our dataset. We want to remove the last digit from this dataset.

Step 1:

First, make a pattern removing the last digit of Cell B5 into Cell C5.

Step 2:

Now, click on Cell C6.
Go to the Data tab.
Select the Flash Fill option.

After selecting the Flash Fill our data becomes the below image.

How easily Flash Fill removed the last digit in Excel.

We can also apply this Flash Fill using the keyboard shortcut. Press the Ctrl+E and the Flash Fill operation will perform.

Note:

If Flash Fill is turned off, then turn on this in the following way.

Go to File>Options then see the below image.

In Excel Options 1st select Advanced.
Then tick the Automatically Flash Fill.
Finally, press OK.

Then Flash Fill will enable.

Similar Readings

5. VBA Macro Code to Remove Last Digit in Excel

We will apply a VBA macro code to remove the last digit in Excel.

We consider the below dataset and the new data will replace here.

Step 1:

First, go to the Developer tab.
Click on Record Macro.
Put Remove_last_digit_1 on the Macro name box.
Then click on OK.

Step 2:

Then click on Macros and select Remove_last_digit_1 from the Macro dialog box.
Then, press the Step Into.

Step 3:

Now, write the below code on the command window.

Sub Remove_last_digit_1()
Dim last As Range
For Each last In Selection
If (Not last.HasFormula) And (Application.WorksheetFunction.IsNumber(last)) Then
last = Left(last, Len(last) - 1)
End If
Next last
End Sub

Step 4:

Now, select the data from the Excel worksheet.

Step 5:

Press the marked tab of the VBA main tab to run the code.
Or you can press the F5 button.

This is our final result.

6. Build a VBA Function to Remove Last Digit

We will build a VBA function to remove the last digit in Excel.

Step 1:

Create a new macro named Remove_last_digit_2.
Then press OK.

Step 2:

Step Into the Remove_last_digit_2 macro following way shown in the previous method. Or press Alt+F8.

Step 3:

Write down the following code on the command window.

Function RemoveLastDigit(input1 As String, num_digit As Long)
RemoveLastDigit = Left(input1, Len(input1) - num_digit)
End Function

Step 4:

Write down the following code on the command window.
Now, save the code and go to the Excel worksheet.
Write the below formula that formed the newly created VBA function.

=RemoveLastDigit(B5,1)

Step 5:

Then press Enter.

Step 6:

Now, drag the Fill Handle icon to get the values of the rest of the cells.

This is a customized function. See the formula we used “1” in the last argument because we wanted to remove only the last digit. If we want to remove more than one digit, then just change this argument as needed.

Similar Readings

Things to Remember

The TRUNC function works only with numeric values. We can not use text here.
When applying the LEN function with other functions must subtract “1” as mentioned in the formula.

Download Practice Workbook

Remove Last Digit.xlsm

Conclusion

We described how to remove the last digit in Excel. We showed some functions, as well as the VBA code, to perform this operation. I hope this will satisfy your needs. If you have any questions or suggestions, feel free to use the comment box.

How to Remove Last Digit in Excel (6 Quick Methods)