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
Read more: How to Remove First Character in Excel
How to Remove the First Character from a String in Excel with VBA: 2 Easy Ways
Here we’ve got a data set with the Names and Student IDs of some students of a school called Sunflower Kindergarten.
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.
⧪ 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.
⧪ 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 end 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 an Excel Macro-Enabled Workbook.
⧪ 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.
⧪ 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.
⧪ Step 8: The Final Output!
You will get the desired number of first characters removed from your selected range of cells.
Read more: How to Remove the Last 3 Characters in Excel
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.
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.
- The function takes two inputs: range and number of characters to be removed.
⧪ 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)
⧪ 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.
Read more: How to Remove First 3 Characters in Excel
Things to Remember
- The Right function of VBA removes character(s) from the left of a given string.
- Alternatively, the Left function removes character(s) from the right, and the MID function removes characters from both sides.
Download Practice workbook
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.
