Sunday, February 16, 2025
No menu items!
HomeData Analytics and VisualizationVBA : How to Remove Special Characters

VBA : How to Remove Special Characters

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.





VBA : Remove Special Characters

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.

RegEx Based Solution

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

Read MoreListenData

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments