# How to Remove Last Digit in Excel [6 Methods]

The elimination of the last digit from a certain cell or the entire column is a common task in Microsoft Excel. It is particularly significant when the unnecessary character is added at the end of desired numbers (such as street number, phone number, registration ID, etc.), then there arises the need to remove the last digit. Fortunately, Excel has a variety of features to accomplish this. Hence, in this article, I will show you 6 different methods to remove the last digit in Excel.

In the following dataset, the **Initial ID** columns contain some messy student IDs. In the **Final ID** column, we will use formulas and **VBA** to show you how to remove the unwanted last characters.

## Remove Last Digit in Excel Using LEFT & LEN Functions

The easiest way to remove the last digit in Excel is by using the **LEFT **& **LEN **functions. The **LEFT **function removes a specified number of characters from the left side of a string. Now follow the steps below to see how it works.

**Syntax**

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

**Formula**

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

**Formula Explanation**

**LEFT:**This is an Excel text function that extracts a specified number of characters from the beginning (left side) of a text string.**B2:**This refers to the cell**B2**in the worksheet. It’s the cell from which you want to extract a portion of text.**LEN(B2):**This part of the formula calculates the length (number of characters) of the text in cell B2. The**LEN**function returns the size of a text string.**LEN(B2) – 1:**This subtracts 1 from the length of the text in cell**B2**. The purpose of subtracting 1 is to remove the last character from the text string.

To remove the last digit in Excel using **LEFT** & **LEN** functions, follow these steps:

- Select cell
**C2**. - Apply the formula:
**=LEFT(B2, LEN(B2)-1)** - Press the
**ENTER**button. - Drag the
**Fill Handle**from cell**C2**to**C10**.

So, the last digit has been removed successfully. See the screenshot:

## Remove the Last Digit in Excel Using the REPLACE & LEN Functions

Another approach to removing the last digit in Excel is by utilizing the **REPLACE **& **LEN **functions. The **REPLACE** function replaces a specified portion of a text string with another text string. The procedures of this method are given below.

**Syntax**

=REPLACE(old_text, LEN(old_text), num_chars, new_text)

**Formula**

=REPLACE(B2,LEN(B2),1,"")

**Formula Explanation**

**REPLACE:**This is an Excel function used for replacing characters within a text string.**B2:**This refers to cell**B2**in the worksheet. It’s the cell from which you want to modify the text.**LEN(B2):**The**LEN**function calculates the length (number of characters) of the text in cell**B2**. In this context, it determines the position of the last character in the text.**1:**This number specifies the number of characters you want to replace. In this case, it’s set to 1, indicating that you want to replace only the last character. (If you want to replace more than one digit, you can edit this number according to yours. )**“”:**This empty double quotation mark effectively means “replace with nothing.” It instructs Excel to remove the character(s) specified in the previous step.

To remove the last digit in Excel using the **REPLACE** & **LEN** functions:

- Select cell
**C2**. - Apply the formula:
**=REPLACE(B2,LEN(B2),1,””)** - Press the
**ENTER**button. - Double-click on the
**Fill Handle**.

By using the **REPLACE** & **LEN** functions, we have removed the last digit. See the image below:

## Remove the Last Digit in Excel with Flash Fill

If your dataset has a fixed pattern, you can take the leverage of the **Flash Fill** tool. Once it recognizes the pattern, it will automatically remove the last digit within a blink of an eye. But the **Flash Fill** feature is available only on **Microsoft Excel 2013** and the later versions.

Follow these steps to remove the last digit in Excel using **Flash Fill:**

- Insert the first value in a cell without the last digit.
- Select the range you want to fill.
- Go to
**Data**. - Select
**Flash Fill**from the**Data Tools**group. Or, click**CTRL+E**.

After applying** Flash Fill**, you will see the result in the Final ID column. We removed the last digit from the Initial ID column.

## Delete the Last Digit from the Numerical String Using the TRUNC Function

If your dataset contains the numerical value, you can follow an alternate way to remove the last digit, **TRUNC** functions. You need to pursue some processes.

**Syntax**

**=TRUNC(number,[num_digits])**

**Formula**

=TRUNC(B2/10)

**Formula Explanation**

**B2/10:**This divides the value of cell**B2**by 10 which puts the last digit after the decimal point. So ID number 1521221 becomes 152122.1.**TRUNC():**This Excel function is used to truncate or remove the fractional portion of a number. So, 1521221 becomes 152122. Thus, the last digit is removed.

To remove the last digit from the numerical string using the **TRUNC** function:

- Select cell
**C2**. - Type this formula:
**=TRUNC(B2/10)** - Press the
**ENTER**button. - Now drag the
**Fill Handle**from cell**C2**to**C10**or double-click on**Fill Handle**.

Here in the initial ID column, there is an undesired number at the end. We have achieved a modified column in Final ID after applying the **TRUNC **function.

## Clear the Last Digit in Excel with VBA Subroutine

To remove the last digit in Excel using **VBA**, follow these steps:

- Right-click on the sheet name and choose
**View Code**to open**Visual Basic Editor**. - Go to
**Insert**>**Module**. - Copy the code below and paste it.
`Sub RemoveLastCharacter() Dim rng As Range Dim cell As Range Dim newValue As String ' Prompt the user to select a range On Error Resume Next Set rng = Application.InputBox("Select a range of cells:", Type:=8) On Error GoTo 0 ' Check if a range was selected If rng Is Nothing Then MsgBox "No range selected. Operation canceled.", vbExclamation Exit Sub End If ' Loop through each cell in the selected range For Each cell In rng If Len(cell.Value) > 0 Then ' Check if the cell is not empty newValue = Left(cell.Value, Len(cell.Value) - 1) cell.Value = newValue End If Next cell MsgBox "Last character removed from the selected range.", vbInformation End Sub`

- Now, move to the worksheet and select the
**Developer**tab. Then, Click on the**Macros**. Alternatively, you can press**ALT+F8**to open the**Macro**dialog box. - Click on
**Run**. - You will have to select a range then.

After clicking on the** OK **button on the Input dialog box, the last digits of the selected cells will disappear.

## Creating User-Defined Function with VBA to Remove Last Digit in Excel

Another approach is to build a user-defined function using **VBA **to remove the last digit in Excel. Here are the steps to follow:

- Right-click on the sheet name and select
**View Code**to open**Visual Basic Editor.** - Go to
**Insert**>**Module**. - Copy the given code and paste it.
`Function ClearLastDigit(inputValue As String) As String If Len(inputValue) > 0 Then ClearLastDigit = Left(inputValue, Len(inputValue) - 1) Else ClearLastDigit = inputValue End If End Function`

- Save the code and go to the Excel worksheet.
- Click on cell
**C2**and apply this formula:**=ClearLastDigit(B2)** - Click on
**Fill Handle**to copy the formula from**C2**to**C10**.

Here we created a user-defined function using **VBA**. Finally, we have removed the last digit successfully after applying it. See the image.

## Conclusion

In this article, I’ve discussed 6 methods to remove the last digit from your dataset. Whether you prefer using built-in functions or creating custom **VBA** macros, these techniques allow you to manipulate your data effectively and streamline your analysis tasks. By mastering these methods, you can become more proficient in Excel and enhance your data processing skills.

## Frequently Asked Questions

### How do I get rid of the last 3 digits in Excel?

To remove the last 3 digits in Excel:

- In an empty cell, use the formula:
**=LEFT(A1, LEN(A1)-3)** - Press
**ENTER**. - Copy the result.
- Right-click on the cell with the original text.
- Select
**Paste Special**. - Choose
**Values**and click**OK**.

### How do I separate the last 4 digits in Excel?

To separate the last 4 digits in Excel:

- Select cell
**A2**. - Use the formula:
**=RIGHT(A1, 4)** - Press
**ENTER**.

The cell containing the formula will now display the last 4 digits from the text in cell **A1**.