How to remove characters in Excel using VBA

by | Oct 12, 2018 | Excel Macros, Excel Tips & Tricks

How to remove characters in Excel using VBA

You can remove characters by replacing a character with an empty string (""). Although you can do this by going through all such cells in a selection or specified range using Find & Replace, in this article we're going to show you how to remove characters in Excel using VBA. This method can help you integrate this with other calculations and automate the process.

 

How to remove characters in Excel using VBA

There are 2 separate functions that we need to look at here:

  • Find
  • Replace

We need to use the Replace method to perform a removing action. For this, there are two parameters we should focus:

  • What: String that we want to remove
  • Replacement: Replacement value, which should be an empty string ("") for removing the characters

You can assign these values into variables, or directly use them as an argument which is the case in the sample code we're going to be using.

Alternatively, you can replace within a predetermined range, instead of a selected range. To do this, replace the Selection object with a Range object. You can find both examples at below.

You can use the code in two ways:

  • Module
  • Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

 

Remove a character in a selection

Module Version:

Sub RemoveCharacterInSelection

      Dim oldValue As String, newValue As String

     

      oldValue = "e"

      newValue = "" 'Because we want to remove

     

      'oldValue and newValue variables are used as arguments

      Selection.Cells.Replace What:= oldValue, Replacement:= newValue, _

            LookAt:=xlPart, SearchOrder:=xlByRows, _

            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

Immediate Window version (no variables):

Selection.Cells.Replace What:="e", Replacement:="", _

            LookAt:=xlPart, SearchOrder:=xlByRows, _

            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

 

Remove a character in a specified range

Module Version:

Sub RemoveCharacterInRange

      Dim oldValue As String, newValue As String, rng as Range

     

      oldValue = "e"

      newValue = "" 'Because we want to remove

      Set rng = Range("B2:E11")

     

      'Replace action takes in rng range

      rng.Cells.Replace What:= oldValue, Replacement:= newValue, _

            LookAt:=xlPart, SearchOrder:=xlByRows, _

            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

Immediate Window version (no variables):

Range("B2:E11").Cells.Replace What:="e", Replacement:="", _

            LookAt:=xlPart, SearchOrder:=xlByRows, _

            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False