# 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.

The dataset I used for this article consists of two columns. First, the **ID Number** column contains some ID numbers of a product. Then, I inserted an empty column named **Final ID number**. So, I will store the IDs after removing the last 3 digits from them.

## Remove Last 3 Digits in Excel Using LEFT & LEN Functions

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)

**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.

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

- Select cell
**B2**. - Apply the formula:
**=LEFT(A2, LEN(A2)-3)** - Press
**ENTER**to insert the formula. - Now drag the
**Fill Handle**from cell**B2**to**B10**.

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

## Remove the Last 3 Digits in Excel Using the MID & LEN Functions

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)

**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**.

To remove the last 3 digits in Excel using the **MID **& **LEN** functions:

- Select cell
**B2**. - Apply the formula:
**=MID(A2,1, LEN(A2) – 3)** - Press
**ENTER**to apply the formula. - Double-click on the
**Fill Handle**.

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

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

**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.

Follow these steps to remove the last 3 digits in Excel using the **Replace **function:

- Select cell
**B2**. - Write this formula:
**=REPLACE(A2, LEN(A2)-2, 3, “”)** - Press the
**ENTER**key to apply the formula. - Drag the
**Fill Handle**to copy the formula to the rest of the cells.

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

## Use the 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), "")

**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.

Follow these steps to remove the last 3 digits in Excel using the **SUBSTITUTE** function:

- Select cell
**B2**. - Apply the formula:
**=SUBSTITUTE(A2, RIGHT(A2, 3), “”)** - Press the
**ENTER**button. - Double-click on the
**Fill Handle**.

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

## 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. For example, this dataset has a pattern and a space before every last **3** digits.

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

Follow the below steps to remove the last **3 **digits with **Flash Fill** in Excel:

- Manually type the cell content of cell
**B2****3**digits. - Select the range you want to fill.
- Go to the
**Home**tab. - Go to
**Editing**group >**Fill**drop-down menu >**Flash Fill**command.

**Shortcut key:** press **CTRL+E**.

Alternatively, you can go to the **Data** tab > **Data Tools** group > **Flash Fill **tool to find** Flash Fill** command.

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

## Remove the Last 3 Digits in Excel Using the VBA Code

**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.

Follow the guide to apply VBA code to remove the last 3 digits:

- Right-click on the worksheet name
**VBA**and select**View Code**to open**Visual Basic Editor**. - Go to
**Insert > Module**. - 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`

- Press
**CTRL + V**to paste the code into the blank module. - Press on the keyboard
**CTRL + S**to save the file and click**No**in the popped-up dialog box. - Save your
**Excel**file as an**Excel Macro-Enabled Workbook**. - Then, type this formula in cell
**B2**:**=RemoveLastThreeDigits(A2)**. - Press
**ENTER**to apply the formula. - Drag the
**Fill Handle**to copy the formula to the rest of the cells.

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

## Apply 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

**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.

Steps to remove the last 3 digits with the **TRUNC** function:

- Type this formula in cell
**B2**:**=TRUNC(A1/1000,0)*1000** - Press
**ENTER**to apply the formula. - Drag the
**Fill Handle**to copy the formula to the rest of the cells.

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.

## Frequently Asked Questions

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

To remove the last digit in Excel:

- Select a cell
**B1**. - Use the formula:
**=LEFT(A1, LEN(A1)-1)** - Press
**ENTER**. - Copy the result from cell
**B1**. - Double-click on the
**Fill Handle**of cell**B1**.

### 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**.

Excellent presentation!