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

Get FREE Advanced Excel Exercises with Solutions!

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)

Use TRUNC Function to Remove Last Digit

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.

Use TRUNC Function to Remove Last Digit

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)

Insert LEFT Function with LEN Function to Remove Last Digit

Step 2:

  • Now, press Enter.

Step 3:

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

Insert LEFT Function with LEN Function to Remove Last Digit

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


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

Combine REPLACE & LEN Function to Remove Last Digit

Step 2:

  • Click on the Enter button.

Step 3:

  • Drag the Fill Handle icon towards the last cell.

Combine REPLACE & LEN Function to Remove Last Digit

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.

Withdraw Last Number Using Excel Flash Fill

Step 2:

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

Withdraw Last Number Using Excel Flash Fill

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.

Withdraw Last Number Using Excel Flash Fill

  • 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


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.

VBA Macro Code to Remove Last Digit in Excel

Step 2:

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

VBA Macro Code to Remove Last Digit in Excel

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

VBA Macro Code to Remove Last Digit in Excel

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.

VBA Macro Code to Remove Last Digit in Excel

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

Build a VBA Function to Remove Last Digit

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.

Build a VBA Function to Remove Last Digit

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


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.


Related Articles

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo