Remove special characters using replace function in VBA Excel – debugvba.com

Remove special character vba excel

Objective 

How to remove special characters using VBA Excel.

Approach

In this VBA Excel automation, we are removing double quotes, single quote and asterisk from all the strings in column A. We are looping through first cell  to last used cell in column A.Then we remove special characters in each cell by using replace function.

Steps

As we can see in the image below, that there we three strings in column.First string “Test String1” had 3 double quotes. Second string “Test String2” had 1 single quote,Last string “Test String3” had had 2 asterisks.Once we execute the VBA code, all the special characters will be removed from these 3 strings. We can see the same on right side in the image.

 

Remove special character vba excel

 

Code

Sub RemoveSpecialCharacters()
Dim MyCell As Range
Dim MyLastRow As Integer


MyLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For Each MyCell In ActiveWorkbook.Sheets("sheet1").Range("A1:A" & MyLastRow)

'Remove double quotes
MyCell.Value = Replace(MyCell.Value, Chr(34), "")

'Remove Single quotes
MyCell.Value = Replace(MyCell.Value, Chr(39), "")


'Remove asterisk
MyCell.Value = Replace(MyCell.Value, Chr(42), "")


Next MyCell

End Sub

 

Notes

  • We can use the nested replace function as well, but to make code easy to understand, I have created separate expressions
  • We can replace any other special character from the strings by following similar approach,we can get the ASCII code of other special characters from internet and use the replace function of VBA excel

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function

Post you may like

Prompt to select a folder to save file in VBA Excel – debugvba.com