In Excel, removing the last character from a string using VBA is quite a simple task to do if you follow the correct steps. In this article, we will show you how you can remove the last character from a string in Excel with VBA in two easy methods. You’ll learn the procedures to remove the last character using a Macro and a User-Defined Function. Now, let’s start this article and explore these methods.
The following animated GIF demonstrates the overview of the methods to remove the last character from a string using VBA in Excel.
How to Remove Last Character from String Using VBA in Excel: 2 Easy Ways
In this section of the article, we will learn two simple methods to remove the last character using VBA in Excel. Let’s say, we have the Student Record of Sunflower Kindergarten as our dataset. In the dataset, we have Student IDs for the students. For demonstration, we will remove the last character from the Student IDs.
1. Using Macro to Remove Last Character from String
Using the VBA Macro feature of Excel is one of the most efficient methods to remove the last character from a string. Now, let’s follow the steps mentioned below to do this.
- Firstly, go to the Developer tab from Ribbon.
- After that, choose the Visual Basic option from the Code group.
As a result, the Microsoft Visual Basic for Applications window will open on your worksheet.
- Now, go to the Insert tab in the Microsoft Visual Basic for Applications window.
- Then, click on the Module option from the drop-down.
Step 02: Write and Save VBA Code
- Firstly, write the following code in the newly created Module.
Sub RemovingLastCharacter()
On Error GoTo Message
Dim p As Integer
p = Int(InputBox("Insert the Number of Last Characters to Remove: "))
For a = 1 To Selection.Rows.Count
For b = 1 To Selection.Columns.Count
Selection.Cells(a, b) = Left(Selection.Cells(a, b), Len(Selection.Cells(a, b)) - p)
Next b
Next a
Exit Sub
Message:
MsgBox "Please insert a valid integer less than or equal to the string length.."
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named Remove_Last_Character.
- After that, we used the On Error statement to display a MsgBox to handle any error.
- Then, we used the InputBox function to take the number of characters to remove from the string from the user and assign it to the variable n.
- Following that, we used two nested For Next loops to remove the last character using the VBA Left and VBA Len functions.
- Then, we closed the For Next loops.
- Now, we used the Exit statement to exit from the sub-procedure.
- After that, we specified the message in the MsgBox for error.
- Finally, we ended the sub-procedure.
- After writing the code, click on the Save option.
Step 03: Run Macro to Remove Characters from String
- Firstly, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Afterward, select the cells of the Student ID column.
- After that, apply the keyboard shortcut ALT + F8 to open the Macro Window.
- Now, in the Macro dialogue box, choose the Remove_Last_Character option.
- Subsequently, click on Run.
As a result, a dialogue box will appear on your worksheet.
- Now, in the dialogue box, enter 1 as we will be removing 1 character from the string.
- Finally, click OK.
Consequently, the last character from the Student IDs will be removed, as demonstrated in the following picture.
Read More: How to Remove Last Character in Excel
2. Utilizing User-Defined Function to Remove Last Character from String
Utilizing the User-Defined Function is another smart way to remove the last character from a string in Excel. Let’s use the instructions outlined below to do this.
Steps:
- Firstly, follow the procedure mentioned in Step 01 of the first method.
- After that, write the code given below in the newly created 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 a = 0 To number_of_rows - 1
For b = 0 To number_of_columns - 1
Output(a, b) = Left(rng(a + 1, b + 1), Len(rng(a + 1, b + 1)) - number)
Next b
Next a
REMOVELASTCHARACTER = Output
End Function
Code Breakdown
- Firstly, we created a function named REMOVELASTCHARACTER and specified the arguments of the function along with their data types.
- Then, we introduced a new variable named Output as Variant.
- Following that, we assigned the count of the number of selected rows and columns in the number_of_rows variable and number_of_columns respectively.
- After that, we used the Redim statement to resize the Output array.
- Then, we used two nested For Next loops to remove the last character from the string using the VBA Left and VBA Len functions.
- Now, we closed the For Next loops.
- Subsequently, we assigned the value of the Output array to the function.
- Finally, we terminated the function.
- Afterward, click on the Save option.
- Following that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- After that, use the following formula in cell D5.
=REMOVELASTCHARACTER(C5:C14,1)Here, the range of cells C5:C14 indicates the cells of the Student ID column.
- Then, press ENTER.
Subsequently, the last character from the cells of the Student ID column will be removed, as shown in the image below.
How to Remove the Last 3 Characters Using VBA in Excel
In the previous methods, we discussed how we can remove only the last character from a string. Now, we will learn how we can remove the last 3 characters from a string using VBA in Excel. You can also use this method to remove n numbers of characters from a string. Now, let’s follow the steps discussed below.
Steps:
- Firstly, follow the steps mentioned in Step 01, and Step 02 of the first method.
- Following that, select the cells of the Student ID column.
- Then, press the keyboard shortcut ALT + F8 to open the Macro dialogue box.
- Now, in the Macro dialogue box, choose the Remove_Last_Character option.
- After that, click on Run.
As a result, the following dialogue box will open the worksheet.
- Now, in the dialogue box, enter 3 as we will remove three characters from the string. Or you can enter any other number according to your requirements.
- Lastly, click OK.
Consequently, the last three characters will be removed, as shown in the following picture.
Read More: How to Remove the Last 3 Characters in Excel
How to Delete First and Last Characters Using VBA in Excel
In this section of the article, we will learn how we can delete the first and the last characters of a string at a time using VBA in Excel. Now, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the instructions outlined in Step 01 of the first method to create a new Module.
- Afterward, write the following code in the newly created Module.
Sub first_and_last_character()
For a = 1 To Selection.Rows.Count
For b = 1 To Selection.Columns.Count
Selection.Cells(a, b) = Left(Selection.Cells _
(a, b), Len(Selection.Cells(a, b)) - 1)
Selection.Cells(i, j) = Right(Selection.Cells _
(a, b), Len(Selection.Cells(a, b)) - 1)
Next b
Next a
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named first_and_last_character.
- Then, we used two nested For Next loops to remove the first and the last character from the string by using VBA Right, and VBA Left functions.
- After that, we closed the two For Next loops.
- Lastly, we ended the sub-procedure.
- After that, click on the Save option.
- Then, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Subsequently, select the cells of the Student ID column.
- Following that, apply the keyboard shortcut ALT + F8 to open the Macro Window.
- Now, in the Macro dialogue box, choose the first_and_last_character option.
- Finally, click on Run.
As a result, the first and the last characters will be removed from the cells of the Student ID column as demonstrated in the image below.
Read More: How to Remove the First Character from a String in Excel with VBA
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Download Practice Workbook
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to Remove the Last Character from a String Using VBA in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.
