VBA to Remove Characters from String in Excel (7 Methods)

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 how to remove characters from any string in Excel utilizing VBA.

Download Practice Workbook

You can download the free practice Excel template from here and practice on your own.

7 Useful Methods to Remove Characters from String in Excel by Implementing VBA

In this section, we will show you 7 the most useful methods to remove characters from the string using VBA in Excel.

1. Remove Case Sensitive Characters from String in Excel by VBA

The Generic Syntax to remove characters from strings using VBA is,

The steps of utilizing VBA macro to remove case sensitive characters from string in Excel are given below,

 Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

  Sub remove_case_sensitive_char()
  Dim var As String
  Dim output As String
  var = "The Winner is AUSTRALlIA"
  'to remove all occurrences of "l" from input string (var)
  output = Replace(var, "l", "")
  MsgBox output
  End Sub

  Your code is now ready to run.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• From the pop-up Macros window, select the Macro Name, remove_case_sensitive_char() -> Run.

There will be a pop-up message box and you will notice that the unwanted “l” from the input string has been removed.

-> Input: The Winner is AUSTRALlIA
-> Output: The Winner is AUSTRALIA

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

2. Remove Non-Case Sensitive Characters from String by VBA in Excel

The steps of utilizing VBA macro to remove non-case sensitive characters from string in Excel are given below,

Steps:

• Like the previous discussion, first, open the Visual Basic Editor, Insert a Module and copy the following code and paste it into the code window.

Sub remove_non_case_sensitive_char()
Dim var1 As String
Dim var2 As String
Dim output As String
var1 = "The Winner is AUSTRALlIA"
var2 = "l" 'this is the char to be removed from input string (var1)
output = Replace(var1, LCase(var2), "")
output = Replace(output, UCase(var2), "")
MsgBox output
End Sub

Your code is now ready to run.

• Run the Macro from the Macros window and you will notice that all case of the letter “L/l” from the input string has been removed in the pop-up message box.

-> Input: The Winner is AUSTRALlIA
-> Output: The Winner is AUSTRAIA

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

3. Erase the First N-Occurrences of Specific Characters from String in Excel using VBA

Suppose, you want to remove only the first N-occurrences of specific characters from the string.

The Generic Syntax to do that is,

The steps of utilizing VBA macro to remove the first N-occurrences of specific characters from the string in Excel are given below,

Steps:

• Like the previous discussion, first, open the Visual Basic Editor, Insert a Module and copy the following code and paste it into the code window.

Sub remove_N_occurence_char()
Dim var1 As String
Dim var2 As String
Dim output As String
var1 = "The Winner is AUSTRAllLIA"
var2 = "l"
'removes the first 2 occurrences of "l" from input string (var1)
output = Replace(var1, var2, "", , 2)
MsgBox output
End Sub

Your code is now ready to run.

• Run the Macro from the Macros window and you will notice that the first 2 occurrences of “l” from the input string have been removed in the pop-up message box.

-> Input: The Winner is AUSTRAllLIA
-> Output: The Winner is AUSTRALIA

Read more: How to Remove Specific Characters in Excel

4. Embed VBA to Delete Characters from Left of String in Excel

The steps of utilizing VBA macro to remove characters from the left of string in Excel are given below,

Steps:

• Like the previous discussion, first, open the Visual Basic Editor, Insert a Module and copy the following code and paste it into the code window.

Sub remove_from_left()
Dim var As String
Dim output As String
var = "01The Winner is AUSTRALIA"
'to remove 2 characters from the left of the input string (var)
output = Right(var, Len(var) - 2)
Debug.Print output
MsgBox output
End Sub

Your code is now ready to run.

• Run the Macro from the Macros window and you will notice that the 2 characters from the left of the input string have been removed in the pop-up message box.

-> Input: 01The Winner is AUSTRALIA
-> Output: The Winner is AUSTRALIA

Read more: How to Remove Characters from Left in Excel

5. Insert VBA to Delete Characters from Right of String in Excel

The steps of utilizing VBA macro to remove characters from the right of string in Excel are given below,

Steps:

• Like the previous discussion, first, open the Visual Basic Editor, Insert a Module and copy the following code and paste it into the code window.

Sub remove_from_right()
Dim var As String
Dim output As String
var = "The Winner is AUSTRALIA01"
'to remove 2 characters from the right of the input string (var)
output = Left(var, Len(var) - 2)
Debug.Print output
MsgBox output
End Sub

Your code is now ready to run.

• Run the Macro from the Macros window and you will notice that the 2 characters from the right of the input string have been removed in the pop-up message box.

-> Input: The Winner is AUSTRALIA01
-> Output: The Winner is AUSTRALIA

Read more: How to Remove Characters in Excel

6. Delete Leading and Trailing Space Characters from String in Excel by Implementing VBA

The steps of utilizing VBA macro to remove the leading and trailing space characters from string in Excel are given below,

Steps:

• Like the previous discussion, first, open the Visual Basic Editor, Insert a Module and copy the following code and paste it into the code window.

Sub remove_space()
Dim var As String
Dim output As String
'following is the input string with leading and trailing spaces
var = "   The Winner is AUSTRALIA   "
output = Trim(var)  'remove leading and trailing spaces
'display output in messageBox
MsgBox output
End Sub

Your code is now ready to run.

• Run the Macro from the Macros window and you will notice that all the leading and trailing characters from the input string have been removed in the pop-up message box.

-> Input: ”   The Winner is AUSTRALIA   ”
-> Output: The Winner is AUSTRALIA

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

7. Eliminate Unwanted Space Characters from String in Excel Using VBA

The steps of utilizing VBA macro to remove all unwanted space characters from the String in Excel are given below,

Steps:

• Like the previous discussion, first, open the Visual Basic Editor, Insert a Module and copy the following code and paste it into the code window.

Sub remove_extra_space()
Dim var As String
Dim output As String
'following is the input string with extra spaces
var = "   The     Winner    is   AUSTRALIA   "
'to remove leading and trailing spaces first
output = Trim(var)
'to replace double spaces with single space
'this step has to be repeated 3 times because it occurred 3 times in our input string (var)
output = Replace(output, "  ", " ")
output = Replace(output, "  ", " ")
output = Replace(output, "  ", " ")
'display output in messageBox
MsgBox output
End Sub

Your code is now ready to run.

• Run the Macro from the Macros window and you will notice that all the unwanted space characters from the input string have been removed in the pop-up message box.

-> Input: ”   The     Winner    is   AUSTRALIA   ”
-> Output: The Winner is AUSTRALIA

Conclusion

This article showed you how to remove case sensitive & non-case sensitive characters from strings, how to delete N-occurred characters in a string, how to delete characters from the left and the right of a string and how to erase all space characters from strings in Excel by utilizing VBA code. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic discussed.

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)