If you deal with massive data in Excel, then there is a chance that you will want to remove some last characters from a dataset. It is a straightforward task if you are working on a few data, but it can become arduous when working on a large dataset.
However, Excel can automate this action using some specialized functions. All you have to do is enter the formula in a particular cell, and the last numbers will be removed from the result. You can then select and copy the formula down so that the last digits can also be removed from other cells in the column.
In this tutorial, we will use the LEFT LENS formula.
=LEFT(Cell, LEN(Cell)-N)
This formula will automatically trim the last digits from the number in an excel spreadsheet. A cell that contains "4567" will be trimmed to "456" if '1' is entered in place of "N" in the LEFT LENS formula.
You can choose to trim one digit or multiple digits. It all depends on what you want. Simply change from "N" to any number of values that you wish to remove.
How to remove the last four digits in Excel
1. Enter the data that you wish to trim the last digits.
2. Locate the cell where you want your result to be displayed. Click inside the cell.
3. The next step is to enter the formula =LEFT(A1, LEN(A1)-1) in the output cell.
You should change from A1 to the cell where you want your result to display.
4. Hit the enter key, and your result will appear immediately.
If you are handling several data, go ahead and copy the formula and paste in other cells that you want to trim out one digit. In this image, I was working on cells B2 – B9.
Now, we intend to remove the last four digits from the numbers and replace "1" in the formula with "4". The formula will now become:
=LEFT(A1, LEN(A1)-4)
Paste the formula in the cell, and the last four digits will be removed from your data.
This formula can also be used to trim the last letters from text.
Drag the green rectangle to the rest of the cells to automatically remove the last 4 digits
Now that you know how to remove the last four digits, let's look at how we can remove the first four digits.
1. Identify your destination for the new digits
2. Enter the following formula on the formula bar and press Enter =RIGHT(A1, LEN(A1)-4)
3. Drag the rectangle to the remaining cells to automatically remove the first four characters
Using the TRUNC Function
The TRUNC function removes the last digits by eliminating the fraction part of the integer. In short, the method divides the number in a cell by multiples of ten and ignores the fraction part when returning the value. It is represented by the syntax:
TRUNC (number,[num_digit]), where;
the number is the reference from which the fraction part will be removed.
number-digit is the number of digits or the fraction that will remain when the formula returns the value.
Since the number is divided by the multiple of ten, the number of zeros indicates how many last digits you want to remove. Hence, the formula will not return any fraction if there are no zeros or the division part is blank.
Steps:
1. Based on your dataset, you can go to the cell next to the number you want to trim the last four digits (say cell C5) and write this formula:
=TRUNC(B5/10000)
2. Press the Enter button to see the last four digits removed from your data in cell B5.
3. You can now use the Fill Handle (+) icon to drag the formula down the column. The formula divides all the numbers in a column and returns values in column C.
Combining the REPLACE and LEN Functions
The REPLACE function in this method can replace your desired number of digits or characters from a string. Whereas the LEN function returns the length of the remaining string in the next column. Therefore, combining the two functions can remove the last four digits in Excel and return the value. The syntax of the combination formula is:
REPLACE(old_text, start_num, num_chars, new_text), where;
old-text is the original string in which you want to replace the last four digits.
start-num is the location of the old text from where the replacement will start.
num-chars is the number of digits you want to replace.
new_text is the return value.
Therefore, to remove the last four digits, you can modify the formula too;
=REPLACE (B5, LEN(B5),4,"")
Steps:
1. Write the formula above in the cell next to the string you want to edit. For example, cell C5.
2. Press the Enter button, and you will see a new string with the last four digits removed.
3. Use the Fill Handle (+) icon to drag the formula down the column and generate a new dataset.
Using Excel Flash Fill
The Excel Flash Fill allows you to automatically fill a column based on your desire and manipulate a dataset. That means you can use the technique to remove the last four digits from a string. However, if the Flash Fill operation is disabled in your Excel, you can enable it by clicking on File > Options > Advanced > Automatically Flash Fill > Ok.
Steps:
1. Make a new column to store the new data set. For example, column C.
2. Go to cell C5 and write a new string of cell B5 while removing the last four digits.
3. Click on cell C6.
4. Go to the Data Tab and select the Flash Fill
5. Once you click on the Flash Fill option, you will see new data in a range of cells in column C.
6. Alternatively, press the Ctrl + R keys on your keyboard to apply the shortcut method.
Using VBA Macro Code
You can also apply VBA macro code to remove the last digits. In this case, you can use the following steps:
1. Go to the Developer Tab in your Excel.
2. Click on Record Macro.
3. Write Remove_last_digit_4 in the Macro name box.
4. Click Ok at the bottom of the box.
5. Click on Macros and select Remote_last_digit_4 from the Macro dialog box.
6. Press the Step Into
7. Copy the code below to the command window:
Sub Remove_last_digit_4()
Dim last As Range
For Each last In Selection
If (Not last. HasFormula) And (Application.WorksheetFunction.IsNumber(last)) Then
last = Left (last, Len(last) - 4)
End If
Next last
End Sub
Code source: exceldemy
8. Next, select the data from your Excel worksheet.
9. You can now press the run icon on the VBA main tab or press the F5 button on your keyboard.
Building VBA Function to Remove the Last Digits
This method allows you to build VBA code if your Excel does not have already-enabled Developer options.
1. First, create a new macro named Remove_last_digit_4 and press OK.
2. Click on the Step Into option or press the Alt + F8 keys altogether.
3. Copy and paste the following code on the command window:
Function RemoveLastDigit(input1 As String, num_digit As Long)
RemoveLastDigit = Left(input1, Len(input1) - num_digit)
End Function
4. Save the code and go back to your Excel worksheet.
5. Write the following formula that forms the new VBA function:
=RemoveLastDigit(B5,4)
6. Press the Enter
7. You can use the Fill Handle (+) icon to drag the formula to the rest of the cells in the column.
I hope you understood this tutorial. Kindly share this post with friends. Thanks for reading!