There are so many scenarios in which you need to remove the last digit in Excel. If you are dealing with less data, then it can be easy to remove the data manually. But if you have a huge chunk of data, it can take ages to get rid of the last digit.
In this tutorial, I will show you different ways that you can use it.
1. Using Left and Len Functions
1. Open the spreadsheet containing the values that you want to eliminate the last digit
2. Create a new column next to the column containing your values
3. Type the following formula and press Okay =LEFT(B2, LEN(B2)-1)
As you can see from the image above, the formula has removed the last digit of the value in cell B2.
4. If you have more cells remaining, you can save time by applying the same formula using flash fill. All you need to do is double-click on the fill handle or hold and drag it down.
2. Flash fill to remove last digit in Excel
Flash fill is the fastest method to remove the last digits in Excel. Follow these simple steps.
1. Create a new helper column
2. In the helper column, write the first value without the last digit
3. Write the second. As you write the second, Excel will automatically prompt you to auto-fill the remaining cells. Click on Enter (If the flash fill option doesn’t appear as you type the second value, move on to the third, and it will appear. )
3. The last digits will be automatically removed from all the cells. You can now copy and paste the new values into your original column. Once you do that, delete the helper column.
3. Using the TRUNC function
Trunc helps to remove digits in the fraction position in Excel.
The syntax can be written as TRUNC(number,[num_digit])
Number: refers to the cell containing the number you wish to remove the last digit from. It can also be a number rather than a reference cell.
Num_digit: Refers to the numbers you wish to be left with after removing the decimal part. For instance, when you indicate 0. Then no numbers will remain on the decimal side.
Steps
1. Click on anblank cell next to the one you wish to remove the last digits.
2. Type in the following formula =TRUNC(B2/10)
3. Double-click on the fill handle to apply the formula to the remaining cells
As you can see from the image above, the Trunc function has removed all the last digits. This method is suitable for integers. If your data is a string, try to use other methods discussed in this article.
Download Practice Sheet
4. VBA code to remove the last Digit in Excel
VBA refers to Visual Basic Application. You can create a custom VBA code to assist you in removing the last digits in Excel.
1. Press Alt+f11 to open the VBA editor. Alternatively, navigate to the developer tab and click on Visual Basic. If your Excel document doesn’t have the developer tab, you can learn how to add it here.
2. Click on Insert>Module
3. Copy and paste this VBA code
Sub RemoveLastDigitFromHighlightedCells()
Dim rng As Range
Dim cell As Range
' Get the currently selected range
Set rng = Selection
' Loop through each cell in the range
For Each cell In rng
' Check if the cell is not empty and contains a numeric value
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
' Remove the last digit
cell.Value = Left(cell.Value, Len(cell.Value) - 1)
End If
Next cell
End Sub
4. Head over to the back of the worksheet. Highlight the values you wish to get rid of the last digits
5. Navigate to the Developers tab, Click on Macros, then run the VBA code.
6. All the last digits will be removed.
5. Using Replace and Len Functions
As the name suggests, Replace function helps to replace characters or Digits from your values or strings.
The Replace Syntax is =REPLACE(old_text, start_num, num_chars, new_text)
Follow these steps to combine replace and Len functions in order to remove the last digit in Excel.
1. Open the spreadsheet containing the file
2. Enter the following formula next to your target cell
=REPLACE(B5,LEN(B5),1,””)
Remember to replace B5 with your target cell
3. Press enter
Excel will replace the last digit automatically.
4. Double-click or drag the fill handle to apply the formula to other cells.
Frequently Asked Questions
1. How to remove the last four digits in Excel
You can remove the last four digits in Excel using Flash fill, Left and Len, Replace and Len, or VBA code.
Let me show you how to quickly remove the last four digits in Excel using Left and Len. Note that this method can remove any type of character, whether numbers or Text.
1. Type the following formula to cell C2
=LEFT(B2,LEN(B2)-4)
2. Press Enter, and Excel will automatically remove the last four digits on cell B2
Double-click on the fill handle or drag it down to apply the changes to the remaining cells.
How to remove the last three digits in Excel
You can remove the last three digits in Excel using Flash fill, Left and Len, Replace and Len, or VBA code.
Let me show you how to quickly remove the last three digits in Excel using Left and Len. Note that this method can remove any type of character, whether numbers or Text.
1. Type the following formula to cell C2
=LEFT(B2,LEN(B2)-3)
2. Press Enter, and Excel will automatically remove the last three digits on cell B2
3. Click, hold down, and drag the fill handle to automatically apply the formula to the remaining cells. Alternatively, you can just double-click the fill handle.
How to remove the last two digits in Excel
You can remove the last two digits in Excel using Flash fill, Left and Len, Replace and Len, or VBA code. But in this example, we will use the Left and Len functions
1. Type the following formula to cell C2
=LEFT(B2,LEN(B2)-2)
2. Press Enter, and Excel will automatically remove the two digits on cell B2
3. You can apply the same formula quickly to the remaining cells by dragging and dropping or double-clicking the fill handle.
Final Thoughts
In this article, you have learned five different ways that you can use to remove the last digit in Excel. You have also learned how to get rid of the last four, three, and two digits from Excel. All the methods work depending on how many you want to remove. Use the practice template to follow along or to practice by yourself.