Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Overview of methods to remove characters from string using VBA in Excel

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

Sample dataset

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.

Using Developer option to insert New Module to remove characters from string using VBA in Excel

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.

Microsoft Visual Basic for Applications window

  • Now, in the Microsoft Visual Basic for Applications window, go to the Insert tab.
  • Then, choose the Module option from the drop-down.

Inserting a new Module

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

Writing VBA code to remove characters from string using VBA in Excel

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.

Saving written VBA code

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.

Running Macro to remove case sensitive characters from string using VBA in Excel

Consequently, the misspelled character “l“, will be removed from the string in cell B17, as shown in the following picture.

Output obtained after using Macro

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.

Sample dataset for method 2

Steps:

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

Writing VBA code to remove non-case sensitive characters from string using VBA in Excel

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

Saving written VBA code 

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

Running Macro to remove non-case sensitive characters from string using VBA in Excel

Subsequently, the characters “lL” will be removed from the string as demonstrated in the following image.

Output obtained after utilizing Macro

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.

Sample dataset for method 3

Steps:

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

Writing VBA code to remove characters from string using VBA in Excel

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

Saving written VBA code

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

Running Macro to remove characters from string using VBA in Excel

Consequently, the characters “ll” will be removed from the string as shown in the following picture.

Output got after using created Macro 

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.

Sample dataset for method 4

Steps:

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

Writing VBA code to remove characters from left of the string using VBA in Excel

Code Breakdown

  • Firstly, we created a sub procedure named remove_from_left.
  • After that, we declared two variables var, and output as String.
  • Then, we assigned the value of cell B17 to the var variable.
  • Now, we used the Right and the Len functions.
  • Finally, we ended the sub procedure.
  • After writing the code, click on the Save option.

Saving VBA code

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

Running Macro to remove characters from the left of the string using VBA in Excel

As a result, the first three characters will be deleted from the string as demonstrated in the following picture.

Output obtained by using Macro

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.

Sample dataset for method 5

Steps:

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

Writing VBA code to remove characters from the right of the string using VBA in Excel

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

Saving written VBA code 

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

Running Macro to remove characters from the right of the string using VBA in Excel

Subsequently, the last two characters from the right of the string will be removed as shown in the image below.

Output got after using Macro

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.

Sample dataset for method 6

Steps:

Sub remove_space()
Dim var As String
Dim output As String
var = Range("B17").Value
Range("B17").Value = Trim(var)
End Sub

Writing VBA code to remove space characters from the using VBA in Excel

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.

Saving VBA code 

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

Running Macro to remove space characters from string using VBA in Excel

Subsequently, the leading and trailing spaces will be removed from the string as demonstrated in the following image.

Output obtained by applying Macro

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.

Sample dataset for method 7

Steps:

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

Writing VBA code to remove extra space characters from string using VBA in Excel

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.

Saving written VBA code

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

Using Macro to remove extra space characters from string using VBA in Excel

Consequently, the unwanted spaces will be removed from the string as shown in the picture below.

Output got by using Macro


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.

Sample dataset for method 8 

Steps:

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

Writing VBA code to remove symbol from string using VBA in Excel

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.

Saving VBA code

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

Running Macro to remove symbol from string using VBA in Excel

As a result, the symbols will be eliminated from the string as demonstrated in the following image.

Output got after applying VBA Macro


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo