Remove Last Character from String in Excel with VBA (2 Easy Ways)

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.

Data Set to Remove Last Character from String with VBA

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.

VBA Window to Remove Last Character from String with VBA

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.

⧪ Code:

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

⧪ Notes:

  • This code produces a Macro called Delete_Last_Character.

VBA Code to Remove Last Character from String with VBA

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Saving Workbook to Remove Last Character from String with VBA

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.

Running Macro to Remove Last Character from String with VBA

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.

Input Box to Remove Last Character from String with VBA

You will get the desired number of last characters removed from your selected range of cells.

Read more: VBA to Remove Characters from String in Excel


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.

Code:

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

VBA Code to Remove Last Character from String with VBA

Note:

  • This code creates a User Defined Function called REMOVELASTCHARACTER.

Step 2:

Save the workbook as Excel Macro-Enabled Workbook.

Come back to your worksheet and select the first cell of a new column and enter this formula:

=REMOVELASTCHARACTER(range,number_of_removing_characters)
  • 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:

=RemoveLastCharacter(C4:C13,1)

User Defined Function to VBA Remove Last Character from String with VBA

Note:

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


Conclusion

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.


Related Articles

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo