Code Library

How to Remove Special Characters from a VBA String

A simple function to remove special characters from a string in VBA. Useful for sanitizing file names, sheet names in Excel, and lots of other stuff.

Mike Wolfe

Dec 23, 2021 • 3 min read

The following function will remove special characters from a string in VBA.

The Code

Check out my article on Python-inspired doc tests if you are interested in the '>>> notation in the function header below. Those sample usage statements in the comments are verifiable with a call to my DocTests procedure.

'---------------------------------------------------------------------------------------
' Procedure : StripSpecChars
' DateTime  : 9/28/2007 - 12/23/2021
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/stripspecchars/
' Purpose   : This function strips special characters from a string.
'---------------------------------------------------------------------------------------
'>>> StripSpecChars("Some Text to strip: Numbers 1-42!")
'Some Text to strip Numbers 142
'>>> StripSpecChars("Some Text to strip: Numbers 1-42!", False, False, "-:")
'SomeTexttostrip:Numbers-
'>>> StripSpecChars("Some Text to strip: Numbers 1-42!", False, True, "!", "_")
'Some_Text_to_strip__Numbers_1_42!
'>>> StripSpecChars("Some Text to strip: Numbers 1-42!", False, True, "", ".^.")
'Some.^.Text.^.to.^.strip.^..^.Numbers.^.1.^.42.^.
'
Function StripSpecChars(TextToStrip As String, _
                        Optional AllowSpaces As Boolean = True, _
                        Optional AllowNums As Boolean = True, _
                        Optional WhiteList As String = vbNullString, _
                        Optional ReplacementText As String = vbNullString) As String

    StripSpecChars = TextToStrip
    
    Dim TextLength As Long
    TextLength = Len(TextToStrip)
    
    Dim i As Long
    i = 1
    Do While i <= TextLength
        Dim CurrChar As String
        CurrChar = Mid(StripSpecChars, i, 1)
        If (CurrChar < "A" Or CurrChar > "z") And _
           (Not IsNumeric(CurrChar) Or Not AllowNums) And _
           (CurrChar <> " " Or Not AllowSpaces) And _
           (Not CurrChar Like "[" & WhiteList & "]") Then
            StripSpecChars = Left(StripSpecChars, i - 1) & ReplacementText & Right(StripSpecChars, TextLength - i)
            TextLength = TextLength - 1 + Len(ReplacementText)
            i = i + Len(ReplacementText)
        Else
            i = i + 1
        End If
    Loop

End Function

Usage

Basic Usage

At its most basic, you simply pass the function a string and it returns the same string with any special characters stripped out:

Replacing Spaces with Underscores

Oftentimes you'll want to retaining letter spacing for readability but eliminate the white space itself. That's easy to do by setting the optional AllowSpaces parameter to False and the ReplacementText parameter to the underscore character:

Replacing ALL Special Characters

If you want to limit the output to letters only (no spaces or numbers), you can do that, too:

Allowing Certain Special Characters

What if there are a few special characters that you want to allow through? You can allow any number of special characters by passing them together in the WhiteList parameter:

Locale Considerations

I've only ever used this with US English locale settings. That said, it should Just Work™ in most situations as it is based on the Option Compare settings you are using in your code. Those settings are themselves based on either the effective code page of the system or the locale settings.

Check out the Option Compare settings link above for details.

Performance Considerations

This code is not intended for use with very large strings (100K characters and up).

The code loops through the string one character at a time and reconstructs a new string one character at a time, too. The processing time is negligible for strings up to several thousand characters. However, it starts slowing down with 100K+ character strings.