Code to replace special character with space (1 Viewer)


Registered User.
Feb 13, 2009
Hi all,

We have a spreadsheet that i upload onto a table, i use on of the fields to build a file path but if there is a special character it causes problems,

i need to replace the below with spaces if they are in my field called path

• < (less than)
• > (greater than)
• : (colon)
• " (double quote)
• / (forward slash)
• \ (backslash)
• | (vertical bar or pipe)
• ? (question mark)
• * (asterisk)
• . (full stop)
• , (commer)

please can someone advise on how i can do this in some sort of update query please
You will need VBScript Regular Expressions (Regex) for this one. Research this.

The pattern in Regex to match the characters you specifically listed will be something like this:

Otherwise, for any special character/non word character it will simply be:

Another method:
1. Save those characters in Dictionary Object
2. Perform a like check first to first ascertain that one of those characters exist:
If SomesString Like "[<>:/|?*.,]" Then
3. If it does, loop through each character and check if they Exist in the Dictionary and Replace() if found.
Have you considered the Replace() function? Syntax is fairly simple and straightforward:
Replace(YourField, ">", "")
To be fair, vbaInt's suggestion to use RegEx is a good one if you need to perform this routinely, but if it's a one-off situation, RegEx is a little over-qualified for this using a sledgehammer to drive a finishing nail.
I once used RegEx when I could have used (and ended up using) InStr. The difference in performance is shocking. As I recall RegEx was more than an order of magnitude slower.
This isn't my code - I found it somewhere on the net and I don't have the link or original author to thank - it works well and is flexible;
Public Function fStripIllegal(strCheck As String, Optional strReplaceWith As String = "") As String

    On Error GoTo StripIllErr
    'illegal file name characters included in default string are    ? [ ] /  = + < > :; * " , '

    Dim intI As Integer
    Dim intPassedString As Integer
    Dim intCheckString As Integer
    Dim strChar As String
    Dim strIllegalChars As String
    Dim intReplaceLen As Integer

    If IsNull(strCheck) Then Exit Function

    strIllegalChars = "?[]/=+<>:;,*" & Chr(34) & Chr(39) & Chr(13) & Chr(10)  'add/remove characters you need removed to this string

    intPassedString = Len(strCheck)
    intCheckString = Len(strIllegalChars)

    intReplaceLen = Len(strReplaceWith)

    If intReplaceLen > 0 Then   'a character has been entered to use as the replacement character
        If intReplaceLen = 1 Then   'check the character itself isn't an illegal character
            If InStr(strIllegalChars, strReplaceWith) > 0 Then
                MsgBox "You can't replace an illegal character with another illegal character", _
                       vbOKOnly + vbExclamation, "Invalid Character"
                fStripIllegal = strCheck
                Exit Function
            End If

        Else   'only one replacement character allowed

            MsgBox "Only one character is allowed as a replacement character", _
                   vbOKOnly + vbExclamation, "Invalid Replacement String"
            fStripIllegal = strCheck
            Exit Function
        End If
    End If

    If intPassedString < intCheckString Then

        For intI = 1 To intCheckString
            strChar = Mid(strIllegalChars, intI, 1)
            If InStr(strCheck, strChar) > 0 Then
                strCheck = Replace(strCheck, strChar, strReplaceWith)
            End If
        Next intI


        For intI = 1 To intPassedString
            strChar = Mid(strIllegalChars, intI, 1)
            If InStr(strCheck, strChar) > 0 Then
                strCheck = Replace(strCheck, strChar, strReplaceWith)
            End If
        Next intI

    End If

    fStripIllegal = Trim(strCheck)

    Exit Function

    MsgBox "The following error occured: " & Err.Number & vbCrLf _
         & Err.Description, vbOKOnly + vbExclamation, "Unexpected Error"

    fStripIllegal = strCheck

    Resume StripIllErrExit

End Function
That's a brilliant piece of code Minty, I've just added it to my routine for extracting data from a Microsoft Access table into many Excel sheets:-

There are a couple bugs in "Make Excel Sheets From Access Table" which customer "Sum" pointed out to me, the downloaded data from eBay:-

The "Master Vehicle List" is in text format, without an ID field. The ID needs to be ADDED as an AutoNumber field. Once that's was corrected the Code worked up to a point, it failed when it got to "Ford" because the next car name "Ford Asia/Oceania" (used for the Excel Sheet Name) contained a special character, in particular "/" so I was looking for some code to remove special characters and found this.

I have Now updated the code on the website "Excel Sheets From Access Table"
Last edited:
Thank you @Uncle Gizmo although as stated I can't claim any authorship.
Funnily enough, excel sheet renaming is one of its primary functions in lots of my apps.
@Minty was looking for code to replace a set of certain characters that can't appear in a Excel sheet name and found this and it worked brilliantly so just wanted to thank you for posting

Users who are viewing this thread

Top Bottom