How to Remove Characters from String Using VBA in Excel?

The sample dataset is the Premier League Point Table of the 2012/13 Season. 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

Method 1 – Removing Case Sensitive Characters from String

Step 1 – Insert New Module

Go to the Developer tab from Ribbon.
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

The Microsoft Visual Basic for Applications window will appear on your worksheet.

Microsoft Visual Basic for Applications window

In the Microsoft Visual Basic for Applications window, go to the Insert tab.
Choose the Module option from the drop-down.

Inserting a new Module

Step 2 – Write and Save the VBA Code

Add the following code in the 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

Created a sub-procedure named remove_case_sensitive_char.
Declared two variables var, and output as String.
Assigned the value of cell B17 to the var variable.
Used the VBA Replace function.
Ended the sub-procedure.

Click on the Save option.

Saving written VBA code

Step 3 – Run Macro to Remove Characters from String

Use the keyboard shortcut ALT + F11 to return to the worksheet.
Use the keyboard shortcut ALT + F8 to open the Macro Window.
In the Macro dialogue box, choose the remove_case_sensitive_char option.
Click on Run.

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

The misspelled character “l” will be removed from the string in cell B17.

Output obtained after using Macro

Method 2 – Deleting Non-Case Sensitive Characters from String

The dataset has “The winner is Manchester UnilLted” instead of “The winner is Manchester United”. We will remove “lL” from the string. Sample dataset for method 2

Steps:

Follow the steps mentioned in Step 1 of the first method.
Add the code given below in the 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

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

Code Breakdown

We created a sub-procedure named remove_non_case_sensitive_char.
Declared three variables var1, var2, and output as String.
Assigned the value of cell B17 to the var1 variable.
Assigned the character that needs to be removed in the var2 variable.
Used the Replace function.
Ended the sub-procedure.

Click on the Save option.

Saving written VBA code

Use the keyboard shortcut ALT + F11 to return to the worksheet.
Use the keyboard shortcut ALT + F8 to open the Macro Window.
In the Macro dialogue box, choose the remove_non_case_sensitive_char option.
Click on Run.

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

The character “lL” will be removed from the string as demonstrated in the following image.

Output obtained after utilizing Macro

Method 3 – Erasing the First N-Occurrences of Specific Characters

Steps:

Use the procedure mentioned in Step 01 of the first method.
Add the code given below in the 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

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

Code Breakdown

Created a sub-procedure named remove_N_occurence_char.
Declared three variables var1, var2, and output as String.
Assigned the value of cell B17 to the var1 variable.
Assign the character that needs to be removed in the var2 variable.
Used the Replace function.
Ended the sub-procedure.

Click on the Save option.

Saving written VBA code

Use the keyboard shortcut ALT + F11 to return to the worksheet.
Use the shortcut ALT + F8 to open the Macro Window.
In the Macro dialogue box, choose the remove_N_occurence_char option.
Click on Run.

Running Macro to remove characters from string using VBA in Excel

The character “ll” will be removed from the string as shown.

Output got after using created Macro

Method 4 – Deleting Characters from the Left of the String

Steps:

Follow the procedure discussed in Step 1 of the first method to create a new Module.
Add the code given below in the 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

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

Code Breakdown

Created a sub-procedure named remove_from_left.
Declared two variables var, and output as String.
Assigned the value of cell B17 to the var variable.
Used the RIGHT and the LEN functions.
Ended the sub-procedure.

Click on the Save option.

Saving VBA code

Use the keyboard shortcut ALT + F11 to return to the worksheet.
Use the shortcut ALT + F8 to open the Macro Window.
In the Macro dialogue box, choose the remove_from_left option.
Click on Run.

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

The first three characters will be deleted from the string.

Output obtained by using Macro

Method 5 – Using VBA to Remove Characters from the Right of String

Steps:

Use the instructions outlined in Step 01 of the first method.
Add the code given below in the 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

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

Code Breakdown

Created a sub-procedure named remove_from_right.
Declared two variables var and output as String.
Assign the value of cell B17 to the var variable.
Used the VBA Left and the VBA Len functions.
Ended the sub-procedure.

Click on the Save option.

Saving written VBA code

Use the keyboard shortcut ALT + F11 to return to the worksheet.
Use the shortcut ALT + F8 to open the Macro Window.
In the Macro dialogue box, choose the remove_from_left option.
Click on Run.

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

The last two characters from the right of the string will be removed.

Output got after using Macro

Read More: Excel Remove Characters From Right

Method 6 – Eradicating Leading and Trailing Spaces from String

Steps:

Follow the steps mentioned in Step 01 of the first method to create a new Module.
Add the code given below in the Module.

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

Created a sub-procedure named remove_space.
Declared two variables var, and output as String.
Assigned the value of cell B17 to the var variable.
Used the VBA Trim function.
Ended the sub-procedure.

Click on the Save option.

Saving VBA code

Use the keyboard shortcut ALT + F11 to return to the worksheet.
Use the shortcut ALT + F8 to open the Macro Window.
In the Macro dialogue box, choose the remove_space option.
Click on Run.

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

The leading and trailing spaces will be removed from the string.

Output obtained by applying Macro

Method 7 – Eliminating Unwanted Spaces from String

Steps:

Use the steps outlined in Step 1 of the first method.
Add the code given below in the 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

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

Code Breakdown

Created a sub-procedure named remove_extra_space.
Declared two variables var, and output as String.
Assigned the value of cell B17 to the var variable.
Used the Trim function.
Used the Replace function.
Assigned the value of output to cell B17.
Ended the sub-procedure.

Click on the Save option.

Saving written VBA code

Use the keyboard shortcut ALT + F11 to return to the worksheet.
Use the shortcut ALT + F8 to open the Macro Window.
In the Macro dialogue box, choose the remove_extra_space option.
Click on Run.

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

The unwanted spaces will be removed from the string.

Output got by using Macro

Method 8 – How to Remove Symbols from String Using VBA in Excel

Steps:

Follow the steps mentioned in Step 1 of the first method to create a new Module.
Add the code given below in the 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

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

Code Breakdown

Created a sub-procedure named removing_symbols.
Declared three variables given_str, symbols as String, and symbol_index as Long.
Assigned the value of cell B17 to the given_str variable.
Assigned the list of symbols in the symbols variable.
Used a For Next loop to remove the symbols from the string.
Used the Replace and the Mid functions.
Assigned the value of given_str to cell B17.
Ended the sub-procedure.