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:
- 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).
- Select any cell in the Result column
- In the ribbon, click on the Data tab
- Click on the Flash Fill option or press CTRL + E from the keyboard
This will automatically follow the pattern in step 1 and remove the last digit from all the entries, as shown in the screenshot below.
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.
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
Copy the formula in the entire column to remove the last digit from the data set.
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
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)
Copy the formula for all the cells in the column.
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)
Also read: Remove the Last 4 Characters in Excel
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
Below is the formula that will remove the last digit from the number in cell A2:
=REPLACE(A2,LEN(A2)-1,3,"")
Copy this formula for all the cells in the column. The result you get would have the last digits removed from all the numbers.
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:
- How to Round to Nearest 100 in Excel? 6 Easy Ways!
- How to Remove Space before Text in Excel
- How to Remove Dollar Sign in Excel
- How to Remove Apostrophe in Excel
- How to Remove Commas in Excel (from Numbers or Text String)
- How to Remove Leading Zeros in Excel
- How to Add Text to the Beginning or End of all Cells in Excel
- How to Remove Decimals in Excel
- How to Remove First Character in Excel?
