How to Remove the Last Digit in Excel?

When cleaning data in Excel, you will often come across situations where you need to remove the last digit from the data.

If you only have a few data entries from which you need to remove the last digit, you can do it manually.

However, if you have a lot of data, doing it manually would be long, tedious, and error-prone.

In this tutorial, I will show you how you can easily remove the last digit from the data in Excel using simple in-built functionalities in Excel or easy formulas.

Method 1: Remove Last Digit Using Flash Fill

There is a great feature in Excel called Flash Fill that can be used to remove the last digit.

Flash Fill works by identifying a pattern and then applying it to the entire column.

Let me show you how it works.

For demonstration purposes, I will use the below numbers dataset, as shown in the below screenshot:

Dataset with numbers
  1. In cell B2, enter the result you want. In this case, I will enter 1324535 (where I have removed the last digit from the value in cell A2).
Enter result in cell B2
  1. Select any cell in the Result column 
Select the second cell
  1. In the ribbon, click on the Data tab
click the data tab
  1. Click on the Flash Fill option or press CTRL + E from the keyboard
Click on Flash Fill

This will automatically follow the pattern in step 1 and remove the last digit from all the entries, as shown in the screenshot below.

Flash Fill result with last digit removed

This is the easiest way to remove the last digit in Excel. Moreover, you can use the Flash Fill option for various purposes where there are some patterns in your data. 

While I have showcased an example of how to remove the last digit from a number in Excel, you can use the same steps to remove any number of digits from a number from the beginning or the end, or any number of characters from a text string from the beginning or the end using Flash Fill.

Note: While Flash Fill is amazing and getting better, there could be times when it fails to identify the pattern correctly. In such as case, you can enter the data manually in two or three cells and then use Flash Fill. That would help it identify the pattern correctly.

Also read: How to Remove Text after a Specific Character in Excel

Method 2: Remove Last Digit Using LEFT and LEN Function

LEFT is a built-in Excel function that returns the specified number of characters or digits from the start of the number or text.

Similarly, the LEN function calculates the number of characters in a cell. In case the cells have numbers, the LEN function will give you the number of digits in the cell.

In this section, I will show you how to use both formulas in combination to remove the last digit from the dataset.

For demonstration purposes, I will use the below dataset, as shown in the screenshot.

dataset

To remove the last digit from the above dataset, use the following formula

LEFT(text, LEN(text)-N)

Where N is the number of digits or characters that you want to remove.

In this case, we want to remove the last digit from the data so the formula for the first entry would become

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

Enter the above formula in Excel as shown below

formula to remove last digit

Copy the formula in the entire column to remove the last digit from the data set.

Result

The formula uses the LEN function to determine the total number of digits in this cell.

We have then used the LEN function within the LEFT function to extract all the digits except the last digit.

To do this, we have subtracted one from the LEN function so that it gives us all the digits till the second-last digit. Hence the result we get is where the last digit has been removed from the numbers in column A.

In the example, we have used a formula to remove the last digit from the numbers in the cells.

You can use the same formula with a minor tweak to remove any number of digits or characters.

For example, if you want to remove the last three digits from a number, you can use the below formula:

=LEFT(A2,LEN(A2)-3)

You can use the above formula to remove the last digit from numbers or remove the last character from text strings. So this would work with numbers as well as text strings

Also read: How to Remove a Specific Character from a String in Excel

Method 3: Remove Last Digit Using TRUNC Function

TRUNC is another built-in function in Excel that is used to remove the fractional part from the text.

We can use the TRUNC function to remove the last digit as well.

To do this, first, we divide the number by 10, converting the last digit into a fractional part. For instance, let’s say we have the number 123453. If we divide it by 10, it becomes 12345.3.

Using the TRUNC function, we can remove the fractional part (last digit).

Let me show you how it works.

For demonstration purposes, I will use the below dataset

Dataset with numbers

Here is the syntax of the TRUNC function

TRUNC(number, [num_digits])

Here, since we only want to remove the last digit, we will divide the numbers by 10, and then use the TRUNC function.

=TRUNC(A2/10)
TRUNC formula

Copy the formula for all the cells in the column.

Last digit removed

You can also use this formula to remove more than one digit.

Note: This method can only remove the last digit from a numeric dataset. If you have a text dataset, you won’t be able to use it (as you can’t divide a text string by 10)

Method 4: Remove Last Digit Using REPLACE and LEN Functions

Another way to remove the last digit in Excel is by using the REPLACE function in conjunction with the LEN function.

Let me show you an example.

For demonstration purposes, I will use the below dataset

Dataset

Below is the formula that will remove the last digit from the number in cell A2:

=REPLACE(A2,LEN(A2)-1,3,"")
REPLACE formula

Copy this formula for all the cells in the column. The result you get would have the last digits removed from all the numbers.

Copy formula for all the cells in the column

In the above formula, we have used the REPLACE function to remove the last digit from the numbers in column A. Since the numbers could be of varying lengths, we first use the LEN function to calculate the total number of digits in the cell.

We then specify LEN(A2)-1 as the starting number from which the replacement should start, and then we asked the REPLACE function to replace only one digit starting from the LEN(A2)-1 position.

The REPLACE function then replaces the last digit with a blank (“”)

You can also use this formula to remove more than one digit from the number. Simply change the number of digits in the above formulas. For instance, if you want to remove three digits from the end, then the formula would become

=REPLACE(A2,LEN(A2)-3,3,"")

In this tutorial, I showed you the different methods for removing the last digit in Excel.

Flash Fill Is great at identifying the pattern and quickly removing the digits or characters from the end. I’ve also covered some formulas that you can use to quickly remove the last digit from a number and extract the rest.

Other articles you may also like: