How to Remove Characters from String Using VBA in Excel

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.

Download Practice Workbook

7 Suitable Methods to Remove Characters from String Using VBA in Excel

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 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 Unilted” 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.

Step 01: Insert New Module 

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

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

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

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 UnilLted” 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

• 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 characters “lL” will be removed from the string as demonstrated in the following image.

Read more: Remove Last Character from String in Excel with VBA

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

• 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 characters “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 Left of 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 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 Unitedds”. 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

• 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: How to Remove Characters in Excel

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

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

Read more: How to Remove Spaces in Excel: With Formula, VBA & Power Query

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

• 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

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

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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.

You May Also Like to Explore

• How to Remove Non-Printable Characters in Excel (4 Easy Ways)
• How to Remove Special Characters in Excel (4 Methods)
• How to Count Rows with VBA in Excel (5 Approaches)
• How to Use VBA to Delete Empty Rows in Excel
• How to Transpose in Excel VBA (3 Methods)
• How to Remove First Character in Excel (6 Methods)