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.

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.

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

• 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

• 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

• 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

• 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

• 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

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

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.

Related Articles

• How to Remove Characters from Left and Right in Excel
• How to Remove Special Characters in Excel
• How to Remove Numeric Characters from Cells in Excel
• How to Remove Non-numeric Characters from Cells in Excel
• How to Remove Characters After a Specific Character in Excel

<< Go Back To Excel Remove Characters | Data Cleaning in Excel | Learn Excel