Remove special characters using replace function in VBA Excel –

Remove special character vba excel


How to remove special characters using VBA Excel.


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.


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



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



  • 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



Post you may like

Prompt to select a folder to save file in VBA Excel –