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
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.
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 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.
⧪ 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.
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.
⧪ 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.
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
- Remove Last Character from String Excel (5 Easy Methods)
- How to Remove Specific Characters in Excel ( 5 Ways)
- How to Remove Specific Text from Cell in Excel (Easiest 11 Ways)
- Remove Special Characters in Excel (4 Methods)
- How to Remove the Last 3 Characters in Excel (4 Formulas)
- How to Remove Non-numeric Characters from Cells in Excel
- Remove First Character from String in Excel (6 Quick Ways)