How to Remove Special Characters from a VBA String
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 FunctionUsage
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.