How to Remove Last Character from String Using VBA in Excel?

The following GIF demonstrates the process of removing the last character from a string using VBA in Excel.

Overview of the methods to Remove Last Character from String Using VBA in Excel

How to Remove Last Character from String Using VBA in Excel: 2 Easy Ways

We have the Student Record of Sunflower Kindergarten as our dataset. We have Student IDs for the students. We will remove the last character from the Student IDs.

Method 1 – Using Macro to Remove Last Character from String

Step 1 – Create New Module

Go to the Developer tab from Ribbon.
Choose the Visual Basic option from the Code group.

The Microsoft Visual Basic for Applications window will open on your worksheet.

Go to the Insert tab in the Microsoft Visual Basic for Applications window.
Click on the Module option from the drop-down.

Step 2 – Write and Save VBA Code

Insert 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

We created a sub-procedure named Remove_Last_Character.
We used the On Error statement to display a MsgBox to handle any error.
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.
We used two nested For Next loops to remove the last character using the VBA Left and VBA Len functions.
We used the Exit statement to exit from the sub-procedure.
We specified the message in the MsgBox for any errors.

Click on the Save option.

Step 3 – Run Macro to Remove Characters from String

Use the keyboard shortcut Alt + F11 to return to the worksheet.
Select the cells of the Student ID column.
Apply the keyboard shortcut Alt + F8 to open the Macro Window.

In the Macro dialog box, choose the Remove_Last_Character option.
Click on Run.

In the input box, enter 1 as we will be removing 1 character from the right of the string.
Click OK.

The last character from the Student IDs will be removed, as demonstrated in the following picture.

Method 2 – Utilizing a User-Defined Function to Remove Last Character from String

Steps:

Open a new Module as in Step 1 of Method 1.
Insert 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

We created a function named REMOVELASTCHARACTER and specified the arguments of the function along with their data types.
We introduced a new variable named Output as Variant.
We assigned the count of the number of selected rows and columns in the number_of_rows variable and number_of_columns, respectively.
We used the Redim statement to resize the Output array.
We used two nested For Next loops to remove the last character from the string using the VBA Left and VBA Len functions.
We assigned the value of the Output array to the function.

Click on the Save option.

Use the keyboard shortcut Alt + F11 to return to the worksheet.
Use the following formula in cell D5.

=REMOVELASTCHARACTER(C5:C14,1)

The range of cells C5:C14 indicates the cells of the Student ID column.

Press Enter.

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

Steps:

Follow Method 1 to apply the macro.
Select the cells of the Student ID column.
Press the keyboard shortcut Alt + F8 to open the Macro dialog box.

Choose the Remove_Last_Character option.
Click on Run.

You’ll get an input box.

Enter 3 as we will remove three characters from the string. You can enter any other number according to your requirements.
Click OK.

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

How to Delete the First and Last Characters Using VBA in Excel

Steps:

Create a new Module (see Step 1 of Method 1).
Insert 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

We created a sub-procedure named first_and_last_character.
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.