Want to remove the last digit in your Excel worksheet? You are in the right place! You can do this with some inbuilt Excel functions.
Here in this article, we will discuss 6 methods on how to remove the last digit in Excel.
We are going to use the following dataset of some random data to explain this article.
How to Remove Last Digit in Excel: 6 Quick Methods
We will explain some methods about how to remove the last digit in Excel.
1. Use TRUNC Function to Remove Last Digit
The TRUNC function removes the fraction part from an integer.
Syntax:
TRUNC(number,[num_digit])
Argument:
number – It is the reference from which the fraction part will be removed.
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.
Read More: How to Remove Last Character in Excel
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.
Read More: How to Remove Characters from Left in Excel
Similar Readings
- How to Remove Characters from Left and Right in Excel
- Excel Remove Characters From Right
- How to Remove Characters After a Specific Character in Excel
- How to Remove Specific Characters in Excel
- How to Remove Special Characters in Excel
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.
Read More: How to Remove First Character in Excel
Similar Readings
- How to Remove First 3 Characters in Excel
- How to Remove the Last 3 Characters in Excel
- How to Remove Numeric Characters from Cells in Excel
- How to Remove Non-numeric Characters from Cells in Excel
- How to Remove Non-Alphanumeric Characters in Excel
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.
Read More: How to Remove Last Character from String Using VBA in Excel
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.
Read More: How to Remove Characters from String Using VBA in Excel
Similar Readings
- How to Remove the First Character from a String in Excel with VBA
- How to Remove Non-Printable Characters in Excel
- How to Remove Asterisk in Excel
- How to Remove Blank Characters in Excel
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
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.