Let’s say you have a list of product codes in Excel, and each code ends with a letter that indicates a specific batch. Your manager asks you to remove the last letter from each product code to standardize them for a new system.
For example, if you have the text string “EXCEL CHAMPS 1,” you should change it to “EXCEL CHAMPS.”
Quick Solution: To do this, you can use a simple formula. In the cell where you want to get the text, type =LEFT(A1, LEN(A1) – 1), assuming the original text is in cell A1.
This formula takes the text in A1 and removes the last character, giving you just the product code without the batch letter. Copy this formula down the column to quickly apply it to all your product codes.
This tutorial will teach us this and all the other methods in detail. So, let’s get started.
Remove the Last Character or (N) from the Left with LEFT + LEN
Use the below steps to write this formula:
- First, enter the LEFT function in a cell, and in the first argument, refer to the cell with the original text.
- Next, in the second argument of LEFT, enter the LEN function.
- After that, in the LEN function, refer to cell A2 and then close the LEN function with the closing parentheses.
- In the end, subtract 1 from the LEN function within the [num_chars] and close the LEFT function.
When you hit enter, you are given a new string derived from the original string after removing the last character.
=LEFT(A1,LEN(A1)-1)
You can also use the TRIM to remove any unwanted space you have after and before the string. Our example has a space between the “Champs” and “1”.
And when you remove the last character, the space stays with the string. See the snapshot below.
That’s why it’s better to use TRIM along with the LEFT and LEN.
Using the MID and LEN Function
In this formula, the MID function returns a substring starting from the first character and ending at the character before the last one.
=MID(A1, 1, LEN(A1) - 1)
To understand this function,
- MID(A1, 1, LEN(A1) – 1): This part uses the MID function to extract a part of the text from cell A1.
- A1: This is the cell containing the text you want to modify.
- 1: This tells the MID function to start extracting from the first character of the text.
- LEN(A1) – 1: This calculates the length of the text in A1 and subtracts 1, giving you the number of characters to extract, which is all characters except the last one.
Create a Custom Function with VBA to Remove Characters from the Left
Here we have a VBA custom function that allows you to remove a specified number of characters from the end of a string.
Function RemoveLastChars(inputString As String, numCharsToRemove As Integer) As String
' Check if the number of characters to remove is greater than the length of the input string
If numCharsToRemove >= Len(inputString) Then
' If so, return an empty string
RemoveLastChars = ""
Else
' Otherwise, return the string with the specified number of characters removed from the end
RemoveLastChars = Left(inputString, Len(inputString) - numCharsToRemove)
End If
End Function
To use this custom function, first, open an Excel workbook and press ALT + F11 to open the VBA editor. Then, insert a new module by right-clicking on any existing modules (or the workbook name) and selecting Insert > Module.
In the end, paste the code into the new module, and close the VBA editor and go back to your workbook. Now, you can now use the RemoveLastChars in your worksheet.
=RemoveLastChars(A1, 1)