How to Remove Last Character from String Using VBA in Excel

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.

Download Practice Workbook

2 Simple Methods to Remove Last Character from String Using VBA in Excel

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.

Step 01: Create New Module

• 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

• 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: VBA to Remove Characters from String 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

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

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 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 requirement.
• Lastly, click OK.

Consequently, the last three characters will be removed as shown in the following picture.

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

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

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

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. You can also have a look at our other helpful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.

Related Articles

• 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)