How to Remove the First Character from a String in Excel with VBA

In this article, I will show you how you can remove the first character from a string in Excel with VBA. You’ll learn two easy procedures to remove the first character using a Macro and a User-Defined Function.


VBA Code to Remove the First Character from a String in Excel (Quick View)

Sub Remove_First_Character()

On Error GoTo Message

Dim n As Integer
n = Int(InputBox("Enter the Number of First Characters to Remove: "))
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        Selection.Cells(i, j) = Right(Selection.Cells(i, j), Len(Selection.Cells(i, j)) - n)
    Next j
Next i

Exit Sub

Message:
    MsgBox Str(n) + " is greater than the length of the strings."

End Sub

VBA Code to Remove the First Character from a String in Excel


Download Practice workbook


2 Easy Ways to Remove the First Character from a String with VBA in Excel

Here we’ve got a data set with the Names and Student IDs of some students of a school called Sunflower Kindergarten.

Data Set to Remove the First Character from a String with VBA in Excel

Today our objective is to remove the first character from the Student IDs with VBA.


1. Develop a Macro to Remove the First Character from a String with VBA in Excel

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: Opening the VBA Window

Press ALT+F11 on your keyboard. The VBA window will open.

Opening the VBA Window to Remove the First Character from a String with VBA in Excel

Step 2: Inserting a New Module

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Step 3: Entering the VBA Code

A new module window called “Module 1” will open. 

Insert the following VBA code in the module.

⧪ Code:

Sub Remove_First_Character()

On Error GoTo Message

Dim n As Integer
n = Int(InputBox("Enter the Number of First Characters to Remove: "))
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        Selection.Cells(i, j) = Right(Selection.Cells(i, j), Len(Selection.Cells(i, j)) - n)
    Next j
Next i

Exit Sub

Message:
    MsgBox Str(n) + " is greater than the length of the strings."

End Sub

Notes:

  • This code produces a Macro called Remove_First_Character.
  • Here it removes not only the first character(s) from a string but also the first digit(s) from a number.

VBA Code to Remove the First Character from a String in Excel

Explanation of the Code:

  • Sub Remove_First_Character() starts a Macro called Remove_First_Character.
  • On Error GoTo Message instructs the code to go to the Label called Message in the 11th line if it faces any error while executing.
  • Dim n As Integer declares a variable of data type Integer, of dimension 1 by 1 (default).
  • n = Int(InputBox(“Enter the Number of First Characters to Remove: “)) asks for an input n, this is the number of characters you want to remove from the left.
  • For i = 1 To Selection.Rows.Count starts an iteration of the number of times equal to the number of rows of your selected range.
  • For j = 1 To Selection.Columns.Count starts an iteration of the number of times equal to the number of columns of your selected range.
  • Selection.Cells(i, j) = Right(Selection.Cells(i, j), Len(Selection.Cells(i, j)) – n) removes the first character(s) of your selected range. This is the most important line.
  • For details, see the Right function of VBA.
  • Next j ends the second for loop.
  • Next i ends the first for loop.
  • Exit Sub instructs the code to come out of the Macro.
  • Message: starts a Label called Message that will be called if the code encounters an error while executing.
  • MsgBox Str(n) + ” is greater than the length of the strings.” instructs the code to show the message if it faces an error.
  • End Sub ends the Macro.

Step 4: Saving the Macro-Enabled Workbook

Next, save the workbook as Excel Macro-Enabled Workbook.

Saving the Macro-Enabled Workbook to Remove the First Character from a String with VBA in Excel

Step 5: Selecting Cells to Remove the First Characters

➤ Return to your worksheet.

➤ Then select the range in your worksheet from where you want to delete the first character. Here I have selected the column Student ID (C4:C13).

Step 6: Running the Macro

➤ Then press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Remove_First_Character (The name of the Macro) and click on Run.

Running Macro to Remove the First Character from a String with VBA in Excel

Step 7: Entering the Input (How Many First Characters You Want to Remove: )

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 the First Character from a String with VBA in Excel

Step 8: The Final Output!

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


2. Create a User Defined Function to Remove the First Character from a String with VBA in Excel

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 first character(s) from your data set elsewhere in your worksheet.

Step 1: The Initial Steps

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 RemoveFirstCharacter(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) = Right(rng(i + 1, j + 1), Len(rng(i + 1, j + 1)) - number)
    Next j
Next i

RemoveFirstCharacter = Output

End Function

Notes:

  • This code creates a User Defined Function called RemoveFirstCaracter.
  • And the function takes two inputs: range and number of characters to be removed.

Code to Remove the First Character from a String with VBA in Excel

Explanation of the Code:

  • Function RemoveFirstCharacter(rng As Variant, number As Integer) starts a User-Defined Function called RemoveFirstCharacter.
  • It takes two inputs- a range of cells called rng, and an integer called number.
  • Dim Output As Variant declares an array of values (of any data type) called Output.
  • number_of_rows = rng.Rows.Count declares an integer called number_of_rows whose value is equal to the number of rows of the range called rng.
  • number_of_columns = rng.Columns.Count declares an integer called number_of_columns whose value is equal to the number of columns of the range called rng.
  • ReDim Output(number_of_rows – 1, number_of_columns – 1) sets the dimension of the array Output to number_of_rows – 1 and number_of_columns – 1.
  • For i = 0 To number_of_rows – 1 starts an iteration of times one less than the integer number_of_rows.
  • For j = 0 To number_of_columns – 1 starts an iteration of times one less than the integer number_of_columns.
  • Output(i, j) = Right(rng(i + 1, j + 1), Len(rng(i + 1, j + 1)) – number) removes the first character(s) from each value of the range rng and sets them as the values of the array Output.
  • For details, see the Right function of VBA.
  • Next j ends the second for loop.
  • Next i ends the first for loop.
  • RemoveFirstCharacter = Output sets the array Output as the output of the function.
  • End Function ends the function.

Step 2: Running the Function

Come back to your worksheet.

Then select the first cell of a new column and enter this formula:

=RemoveFirstCharacter(range,number of characters to be removed)
  • Here, range is the range of strings whose first character(s) you want to remove. In this example, it is C4:C13 (Student ID).
  • And number of characters to be removed is the number of characters you want to remove from the left. In this example, it is 1.

Therefore, the formula will be:

=RemoveFirstCharacter(C4:C13,1)
[It’s an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.]

User Defined Function to Remove the First Character from a String with VBA in Excel

Step 3: The Final Output

You will find an array of strings created in your selected cells, after removing the first character(s) from your input range.


Things to Remember

  • The Right function of VBA removes character(s) from the left of a given string.
  • And alternatively, the Left function removes character(s) from the right, and the MID function removes characters from both sides.

Conclusion

Using these methods, you can remove the first character(s) from a string with VBA in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Readings

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