Sometimes we need to remove certain characters from strings of a large dataset in Excel. Implementing VBA Macro is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you seven easy methods to remove characters from any string in Excel using VBA. Now, let’s start this article and explore these methods.
How to Remove Characters from String Using VBA in Excel: 7 Easy Ways
In this section of the article, we will discuss seven useful methods to remove characters from a string by using VBA in Excel. Let’s say, we have the Premier League Point Table of the 2012/13 Season as our dataset. In the dataset, we have Manchester United as the Champion Team. But in cell B17, we can see that it is spelled “Manchester United” instead of “Manchester United”. Our goal is to remove the misspelled character (” l”).
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Removing Case Sensitive Characters from String
In the first method, we will remove a case-sensitive character from a string using VBA in Excel. Now, let’s follow the steps mentioned in the following section to do this.
- Firstly, go to the Developer tab from Ribbon.
- After that, click on the Visual Basic option from the Code group.
As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.
- Now, in the Microsoft Visual Basic for Applications window, go to the Insert tab.
- Then, choose the Module option from the drop-down.
Step 02: Write and Save the VBA Code
- Firstly, write the following code in the newly created Module.
Sub remove_case_sensitive_char()
Dim var As String
Dim output As String
var = Range("B17").Value
Range("B17").Value = Replace(var, "l", "")
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named remove_case_sensitive_char.
- After that, we declared two variables var, and output as String.
- Then, we assigned the value of cell B17 to the var variable.
- Following that, we used the VBA Replace function.
- Finally, we ended the sub-procedure.
- After writing the code, click on the Save option.
Step 03: Run Macro to Remove Characters from String
- Firstly, use the keyboard shortcut ALT + F11 to return to the worksheet.
- After that, use the keyboard shortcut ALT + F8 to open the Macro Window.
- Now, in the Macro dialogue box, choose the remove_case_sensitive_char option.
- Finally, click on Run.
Consequently, the misspelled character “l“, will be removed from the string in cell B17, as shown in the following picture.
2. Deleting Non-Case Sensitive Characters from String
In this method, we will remove non-case-sensitive characters from the string using VBA in Excel. That means, both the upper case and lower case characters will be removed. For instance, we have “The winner is Manchester United” instead of “The winner is Manchester United”. So, we will remove “lL” from the string. Here, we have both an uppercase L and a lowercase L. The code that we will use in this method will be sufficient to remove both of them from the string. Now, let’s use the instructions outlined below.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method.
- After that, write the code given below in the newly created Module.
Sub remove_non_case_sensitive_char()
Dim var1 As String
Dim var2 As String
Dim output As String
var1 = Range("B17").Value
var2 = "l"
output = Replace(var1, LCase(var2), "")
Range("B17").Value = Replace(output, UCase(var2), "")
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named remove_non_case_sensitive_char.
- Following that, we declared three variables var1, var2, and output as String.
- Then, we assigned the value of cell B17 to the var1 variable.
- Now, we assign the character that needs to be removed in the var2 variable.
- Then, we used the Replace function.
- Lastly, we ended the sub-procedure.
- Following that, click on the Save option.
- Following that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Then, use the keyboard shortcut ALT + F8 to open the Macro Window.
- After that, in the Macro dialogue box, choose the remove_non_case_sensitive_char option.
- Lastly, click on Run.
Subsequently, the character “lL” will be removed from the string as demonstrated in the following image.
3. Erasing the First N-Occurrences of Specific Characters
Now, we will erase the first N-occurrences of specific characters from a string using VBA in Excel. Let’s say, we have “The Winner is Manchester Unillted” instead of “The Winner is Manchester United”. Our goal is to remove the two misspelled “l” characters from the string in cell B17 using VBA in Excel. So, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the procedure mentioned in Step 01 of the first method.
- After that, write the code given below in the newly created Module.
Sub remove_N_occurence_char()
Dim var1 As String
Dim var2 As String
Dim output As String
var1 = Range("B17").Value
var2 = "l"
Range("B17").Value = Replace(var1, var2, "", , 2)
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named remove_N_occurence_char.
- Following that, we declared three variables var1, var2, and output as String.
- Then, we assigned the value of cell B17 to the var1 variable.
- Now, we assign the character that needs to be removed in the var2 variable.
- Then, we used the Replace function.
- Lastly, we ended the sub-procedure.
- After that, click on the Save option.
- Following that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Now, use the keyboard shortcut ALT + F8 to open the Macro Window.
- Subsequently, in the Macro dialogue box, choose the remove_N_occurence_char option.
- Finally, click on Run.
Consequently, the character “ll” will be removed from the string as shown in the following picture.
Read More: How to Remove Specific Characters in Excel
4. Deleting Characters from the Left of the String
In this section of the article, we will delete characters from the left of the string in cell B17 using VBA in Excel. Here, we have “01. The Winner is Manchester United”. We will remove the first three characters from the string. Now, let’s follow the instructions given below.
Steps:
- Firstly, follow the procedure discussed in Step 01 of the first method to create a new Module.
- Then, write the code given below in the newly created Module.
Sub remove_from_left()
Dim var As String
Dim output As String
var = Range("B17").Value
Range("B17").Value = Right(var, Len(var) - 3)
End Sub
- After writing the code, click on the Save option.
- After that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Then, use the keyboard shortcut ALT + F8 to open the Macro Window.
- Now, in the Macro dialogue box, choose the remove_from_left option.
- Lastly, click on Run.
As a result, the first three characters will be deleted from the string as demonstrated in the following picture.
Read More: How to Remove Characters from Left in Excel
5. Using VBA to Remove Characters from the Right of String
In this method, we will remove characters from the right of the string given in cell B17 using VBA in Excel. Here, we have “The Winner is Manchester United”. Our goal is to remove the last two characters from the right of the string. So, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the instructions outlined in Step 01 of the first method.
- Following that, write the code given below in the newly created Module.
Sub remove_from_right()
Dim var As String
Dim output As String
var = Range("B17").Value
Range("B17").Value = Left(var, Len(var) - 2)
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named remove_from_right.
- Following that, we declared two variables var and output as String.
- Now, we assign the value of cell B17 to the var variable.
- Subsequently, we used the VBA Left and the VBA Len functions.
- Lastly, we ended the sub-procedure.
- Then, click on the Save option.
- Now, use the keyboard shortcut ALT + F11 to return to the worksheet.
- After that, use the keyboard shortcut ALT + F8 to open the Macro Window.
- Subsequently, in the Macro dialogue box, choose the remove_from_right option.
- Finally, click on Run.
Subsequently, the last two characters from the right of the string will be removed as shown in the image below.
Read More: Excel Remove Characters From Right
6. Eradicating Leading and Trailing Spaces from String
In this section of the article, we will eradicate the leading and trailing spaces from the string in cell B17 using VBA in Excel. Here, we have three leading spaces and three trailing spaces in the string. Now, let’s use the procedure discussed in the following section to remove these spaces.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method to create a new Module.
- After that, write the code given below in the newly created Module.
Sub remove_space()
Dim var As String
Dim output As String
var = Range("B17").Value
Range("B17").Value = Trim(var)
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named remove_space.
- Then, we declared two variables var, and output as String.
- After that, we assigned the value of cell B17 to the var variable.
- Following that, we used the VBA Trim function.
- Finally, we ended the sub-procedure.
- After writing the code, click on the Save option.
- Following that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Then, use the keyboard shortcut ALT + F8 to open the Macro Window.
- After that, in the Macro dialogue box, choose the remove_space option.
- Lastly, click on Run.
Subsequently, the leading and trailing spaces will be removed from the string as demonstrated in the following image.
7. Eliminating Unwanted Spaces from String
Now, we will learn how we can eliminate unwanted spaces from a string. Here, a string is given in cell B17. We can see that, there are some extra spaces in that string. Our goal is to remove these extra spaces from the string using VBA in Excel. Now, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the steps outlined in Step 01 of the first method.
- Following that, write the code given below in the newly created Module.
Sub remove_extra_space()
Dim var As String
Dim output As String
var = Range("B17").Value
output = Trim(var)
output = Replace(output, " ", " ")
output = Replace(output, " ", " ")
output = Replace(output, " ", " ")
Range("B17").Value = output
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named remove_extra_space.
- Then, we declared two variables var, and output as String.
- After that, we assigned the value of cell B17 to the var variable.
- Following that, we used the Trim function.
- Now, we used the Replace function.
- Afterward, we assigned the value of output to cell B17.
- Lastly, we ended the sub-procedure.
- After that, click on the Save option.
- Then, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Now, use the keyboard shortcut ALT + F8 to open the Macro Window.
- Afterward, in the Macro dialogue box, choose the remove_extra_space option.
- Finally, click on Run.
Consequently, the unwanted spaces will be removed from the string as shown in the picture below.
How to Remove Symbols from String Using VBA in Excel
In the previous methods, we have learned various ways to remove characters from a string using VBA in Excel. But, now, we will learn how we can remove symbols from a string using VBA. For instance, we have “The Winner is #*Manchester United*#” in cell B17. Our goal is to remove these symbols from the string using VBA in Excel.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method to create a new Module.
- Then, write the code given below in the newly created Module.
Sub removing_symbols()
Dim given_str As String
Dim symbols As String
Dim symbol_index As Long
given_str = Range("B17").Value
symbols = "?¿!¡*%#$(){}[]^&/\~+-|€<>"
For symbol_index = 1 To Len(symbols)
given_str = Replace(given_str, Mid(symbols, symbol_index, 1), "")
Next
Range("B17").Value = given_str
End Sub
Code Breakdown
- Firstly, we created a sub-procedure named removing_symbols.
- Then, we declared three variables given_str, symbols as String, and symbol_index as Long.
- After that, we assigned the value of cell B17 to the given_str variable.
- Then, we assigned the list of symbols in the symbols variable.
- Subsequently, we used a For Next loop to remove the symbols from the string.
- In the For Next loop, we used the Replace and the Mid functions.
- Afterward, we assigned the value of given_str to cell B17.
- Lastly, we ended the sub-procedure.
- After writing the code, click on the Save option.
- After that, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Then, use the keyboard shortcut ALT + F8 to open the Macro Window.
- Subsequently, in the Macro dialogue box, choose the removing_symbols option.
- Lastly, click on Run.
As a result, the symbols will be eliminated from the string as demonstrated in the following image.
Download Practice Workbook
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to remove characters from a string by using VBA in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.
