This tutorial explains how to remove special characters in Excel using VBA.
The following VBA code loops through column A and checks if it matches [A-Za-z0-9] (letters and numbers) and then it returns a new word excluding bad characters in column B.
You can download the following dataset to practice.
Sub StripSpecialChar()
Dim lastRow As Long, i As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
Cells(i, 2).Value = CleanChar(Cells(i, 1).Value)
Next i
End Sub
Function CleanChar(Txt As String) As String
Dim i As Integer
Dim newTxt As String
Dim ch As String
newTxt = ""
For i = 1 To Len(Txt)
ch = Mid(Txt, i, 1)
If ch Like "[A-Za-z0-9]" Then
newTxt = newTxt & ch
End If
Next i
CleanChar = newTxt
End Function
This method works on both Windows and Mac operating system.
Efficient Solution Using Regular Expression
This method is faster than the previous solution but it does not work on Mac operating system as it requires regex dependency.
Sub StripSpecialChar2()
Dim lastRow As Long, i As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
Cells(i, 2).Value = CleanChar(Cells(i, 1).Value)
Next i
End Sub
Function CleanChar2(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[^a-zA-Z0-9]"
CleanChar = .Replace(Txt, "")
End With
End Function
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Share Share Tweet