# 7 Ways to Remove Last 3 Digits in Excel

One common requirement that arises during data processing is the need to remove the last three digits from numbers or text. Whether you are dealing with large datasets or a few cells, Excel provides multiple approaches to accomplishing this task efficiently. In this article, I will explore 7 different ways to remove the last **3** digits in Excel.

### Introduction to the Dataset

The dataset I used for this article consists of two columns.

**ID Number:** This column contains some ID numbers of a product.

**Final ID Number:** It contains the IDs after removing the last **3** digits from them.

## Easiest Way to Remove Last 3 Digits in Excel

A simple way to remove the last 3 digits in Excel is by using the **LEFT** and **LEN **functions. The **LEFT **function is used to extract a specified number of characters from the beginning of a text string. The** LEN** function calculates the length of a text string.

**Syntax**

**=LEFT(string, LEN(string)-number_of_chars_to_remove)**

**Formula**

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

**Usage Guide**

**Step_1: **First, copy the formula from above.

**Step_2: **Secondly, paste it into cell **B2**.

**Step_3:** Press **ENTER** to insert the formula.

**Formula Explanation**

**LEFT:**This is an Excel function that extracts a specified number of characters from the left (beginning) of a text string.**A2:**This is the cell reference to the text string from which we want to extract characters. In this case, it refers to cell**A2**.**LEN(A2):**The**LEN**function is used to determine the length of the text string in cell**A2**, including spaces and characters.**LEN(A2) – 3:**By subtracting**3**from the length of the text string, we are effectively excluding the last three characters from the extracted result.

**Step_4: **Now drag the **Fill Handle** to copy the formula to the rest of the cells.

**Final Result >**

Thus, using this formula, you can get the filtered dataset where the last **3** digits are removed. See the **Final Result**:

### Alternative Method #1: Use the MID & LEN Functions to Remove the Last 3 Digits in Excel

The **MID** function in** Excel **is used to extract a specific substring from a text string. It allows you to specify the starting position and the number of characters you want to extract. By using the **MID** function combined with the **LEN** function, you can remove the last **3** digits in **Excel**.

**Syntax**

=MID(text, start_num, LEN(text)-3)

**Formula**

=MID(A2,1, LEN(A2) - 3)

**Usage Guide**

**Step_1: **First, copy the formula from above.

**Step_2: **Secondly, paste it into cell **B2**.

**Step_3:** Press **ENTER** to insert the formula.

**Formula Explanation**

**MID:**This function in Excel is used to extract a specified number of characters from within a text string. It requires three arguments: the text string, the starting position, and the number of characters to extract.**A2:**This is the cell reference that contains the text from which you want to extract a substring.**LEN(A2) – 3:**This part calculates the length of the text in cell**A2**using the**LEN**function and then subtracts**3**from it. This value is used as the starting position for the**MID**.

**Step_4:** Next drag the **Fill Handle** to copy the formula to the rest of the cells.

**Final Result >**

This is an alternative way to remove the last **3** digits from a text string.

### Alternative Method #2: Clear the Last 3 Digits Using the REPLACE Function

The **REPLACE** function in Excel is used to replace a specified number of characters within a text string with new characters. It’s commonly used for manipulating and modifying text strings within cells. By using the** REPLACE** function with the **LEN** function, you can also remove the last 3 digits in **Excel**.

**Syntax**

=REPLACE(old_text,LEN(old_text)-3)

**Formula**

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

**Usage Guide**

**Step_1: **First, copy the formula from above.

**Step_2: **Secondly, paste it into cell **B2**.

**Step_3:** Press **ENTER** to insert the formula.

**Formula Explanation**

**REPLACE:**This is an Excel function that allows you to replace a portion of text within a given text string.**A2:**This is the cell reference that contains the original text.**LEN(A2) – 2:**This part calculates the starting position for the replacement. It subtracts**2**from the length of the text in cell**A2**. This effectively positions the replacement starting from the third-to-last character of the text.**3:**This specifies the number of characters to replace, starting from the position calculated above.**“”**: This is the replacement text, in this case, an empty string. This means that the specified portion of the text will be removed from the original text.

**Step_4:** Drag the **Fill Handle** to copy the formula to the rest of the cells.

**Final Result >**

This is how using this formula, you can get the filtered dataset where the last** 3** digits are removed by the** REPLACE** function.

### Alternative Method #3: Use SUBSTITUTE Function to Remove the Last 3 Digits in Excel

In Excel, the **SUBSTITUTE** function is used to replace occurrences of a specific substring within a text with another substring. By using it with the **RIGHT** function, you can remove the last **3 **digits using **Excel**.

**Syntax**

=SUBSTITUTE(text, old_text, new_text, [instance_num]

**Formula**

=SUBSTITUTE(A2, RIGHT(A2, 3), "")

**Usage Guide**

**Step_1: **First, copy the formula from above.

**Step_2: **Secondly, paste it into cell **B2**.

**Step_3:** Press **ENTER** to insert the formula.

**Formula Explanation**

The **SUBSTITUTE **function in Excel is used to substitute one text string with another within a given text string. It requires three arguments: the original text, the text you want to replace, and the text you want to replace it with.

**A2**: This is the cell reference that contains the original text.**RIGHT(A2, 3)**: This part extracts the rightmost**3**characters from the text in cell**A2**using the**RIGHT**This is the portion of text that you want to replace.**“”**: This is the replacement text, in this case, an empty string. This means that the extracted portion of the text will be removed from the original text.

**Step_4:** Drag the **Fill Handle** to copy the formula to the rest of the cells.

**Final Result >**

Now, using this formula, you can get the filtered dataset where the last **3** digits are removed.

### Alternative Method #4: Use Flash Fill to Remove the Last 3 Digits in Excel

If your dataset contains a specific pattern, you can use the **Flash Fill** feature to remove the last **3** digits without any formula.

**Note:** It is available only on **Microsoft Excel 2013** and later versions.

For example, this dataset has a pattern and a space before every last **3** digits.

**Usage Guide**

**Step_1: **At the top cell, cell **B2 **of the** Final ID Number **column**,** manually write the **ID Number **without the last **3** digits.

**Step_2:** Press the **ENTER** key on the keyboard.

**Step_3: **At the top of the Excel Menu bar, find **Fill, **and then click** Flash Fill**.

**Tip: **You can also find **Flash Fill** on the **Data** tab> **Data Tools **group> **Flash Fill**.

**Final Result >**

Using the** Flash Fill, **you can get the filtered dataset where the last **3** digits are removed. See Screenshot:

### Alternative Method #5: Use VBA Code to Remove the Last 3 Digits in Excel

**VBA** is the short form of **Visual Basic for Applications**. By creating a custom **VBA **user-defined function, you can instantly remove the last 3 from a given dataset.

**Usage Guide**

**Step_1:** Right-click on the worksheet name (**VBA **for my example) and choose **View Code.** A **Visual Basic Editor** will open.

**Step_2: **Go to **Insert > Module**.

**Step_3: **Copy the following code:

```
Function RemoveLastThreeDigits(inputText As String) As String
If Len(inputText) <= 3 Then
RemoveLastThreeDigits = ""
Else
RemoveLastThreeDigits = Left(inputText, Len(inputText) - 3)
End If
End Function
```

**Step_4: **Press **CTRL + V** to paste the code into the blank module that I’ve just created.

**Step_5:** Press on the keyboard **CTRL + S** to save the file.

A dialog box will pop up.

**Step_6:** Click **No**.

**Step_7: **Save your **Excel** file as an **Excel Macro-Enabled Workbook**.

Now let’s remove the last **3** digits using the function **RemoveLastThreeDigits(A2)**.

**Usage Guide**

**Step_1:** Write this formula in cell **B2**.

**Step_2: **Press** ENTER** to insert the formula.

**Step_3:** Drag the **Fill Handle** to copy the formula to the rest of the cells.

**Final Result >**

In this way, you can get the filtered dataset where the last **3** digits are removed.

### Alternative Method #6: Use the TRUNC Function to Remove the Last 3 Digits in Excel

The **TRUNC** function in **Excel** is used to truncate a number by removing the decimal portion. It takes two arguments: the number you want to truncate and an optional number of digits to preserve after truncating.

Then, simply cut off the decimal portion of the number, leaving only the integer part or the specified number of decimal places.

Here is the dataset that I am going to use for this example of demonstrating the **TRUNC** function:

**Syntax**

=TRUNC(number, [num_digits])

**Formula**

=TRUNC(A1/1000,0)*1000

**Usage Guide >**

**Step_1: **First, copy the formula from above.

**Step_2: **Secondly, paste it into cell **B2**.

**Step_3:** Press **ENTER** to insert the formula.

**Formula Explanation**

**A2**refers to the cell containing the number from which you want to remove the last three digits.**A2/1000**divides the number in cell**A2**by**1000**. Dividing by**1000**effectively removes the last three digits from the number.**TRUNC(A2/1000, 0)**is then used to truncate the result of the division to 0 decimal places.

**Step_4: **Drag the **Fill Handle** to copy the formula to the rest of the cells.

**Final Result >**

Thus, using this formula, you can get the filtered dataset where the last **3 **digits are removed.

## Conclusion

In conclusion, Microsoft Excel provides multiple methods to remove the last **3** digits from text and numeric data. Depending on the type of data and your specific requirements, you can choose the method that suits you best.