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.
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
