In this article, I will show you how you can remove last character from string in Excel with VBA. You’ll learn the procedures to remove the last character using a Macro and a User-Defined Function.
Download Practice Workbook
2 Methods to Remove the Last Character from a String with VBA
Here we’ve got a data set with the Names of some students and their Student IDs of a school called Sunflower Kindergarten.
Notice carefully and you will find that there is a character “D” at the end of the Student ID of each student.
Today our objective is to remove this last character “D” from the ID of each student using a VBA Code.
1. Run a Macro to Remove the Last Character from a String with VBA
You can create and run a Macro using a VBA code to remove the last character from a string or a range of strings.
⧪ Step 1:
➤ Press ALT+F11 on your keyboard. The VBA window will open.
⧪ Step 2:
➤ Go to the Insert tab in the VBA window.
➤ From the options available, select Module.
⧪ Step 3:
➤ A new module window called “Module 1” will open.
➤ Insert the following VBA code in the module.
Sub Remove_Last_Character() On Error GoTo Message Dim n As Integer n = Int(InputBox("Enter the Number of Last Characters to Remove: ")) For i = 1 To Selection.Rows.Count For j = 1 To Selection.Columns.Count Selection.Cells(i, j) = Left(Selection.Cells(i, j), Len(Selection.Cells(i, j)) - n) Next j Next i Exit Sub Message: MsgBox "Please Enter a Valid Integer Less than or Equal to the Length of the Strings." End Sub
- This code produces a Macro called Delete_Last_Character.
⧪ Step 4:
⧪ Step 5:
➤ Return to your worksheet.
➤ Select the range in your worksheet from where you want to delete the last character. Here I have selected the column Student ID (C4:C13).
⧪ Step 6:
➤ Then press ALT+F8 on your keyboard.
➤ A dialogue box called Macro will open. Select Remove_Last_Character (The name of the Macro) and click on Run.
⧪ Step 7:
➤ An Input Box will appear asking you to enter the number of characters you want to remove.
➤ For the sake of this example, I have entered 1.
You will get the desired number of last characters removed from your selected range of cells.
2. Create a User Defined Function to Remove the Last Character from a String with VBA
If you don’t want to change the original data set, you can create a User Defined Function using a VBA code.
And then use the function to remove the last character from your data set elsewhere in your worksheet.
⧪ Step 1:
➤ Run Steps 1-3 from the previous method to open the VBA window and insert a new module.
➤ Then insert the following code in the module.
Function RemoveLastCharacter(rng As Variant, number As Integer) Dim Output As Variant number_of_rows = rng.Rows.Count number_of_columns = rng.Columns.Count ReDim Output(number_of_rows - 1, number_of_columns - 1) For i = 0 To number_of_rows - 1 For j = 0 To number_of_columns - 1 Output(i, j) = Left(rng(i + 1, j + 1), Len(rng(i + 1, j + 1)) - number) Next j Next i RemoveLastCharacter = Output End Function
- This code creates a User Defined Function called REMOVELASTCHARACTER.
⧪ Step 2:
➤ Come back to your worksheet and select the first cell of a new column and enter this formula:
- Here, range is the range of strings whose last character you want to remove. In this example, it is C4:C13 (Student ID).
- Number_of_removing_charaters is the number of characters you want to remove from the last. In this example, it is 1.
Therefore, the formula will be:
- It’s an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.
You will find an array of strings created in your selected cells, by removing the last character from your given range.
Using these methods, you can remove the last character from a string with VBA in Excel. Do you have any problems? Feel free to ask us.
- How to Remove Leading Spaces in Excel (4 Methods)
- How to Remove the First Character from a String in Excel with VBA
- How to Remove Spaces in Excel: With Formula, VBA & Power Query
- How to Remove Characters in Excel (6 Methods)
- How to Remove Non-numeric Characters from Cells in Excel
- How to Remove the Last 3 Characters in Excel (4 Formulas)