Code to replace special character with space

[james_halliwell]

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

 

[vbaInet]

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:

\W

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.

 

[NauticalGent]

Have you considered the Replace() function? Syntax is fairly simple and straightforward:
Replace(YourField, ">", "")

 

[NauticalGent]

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 task...like using a sledgehammer to drive a finishing nail.

 

[sneuberg]

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.

 

[Minty]

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

    Else

        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)

StripIllErrExit:
    Exit Function

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

    fStripIllegal = strCheck

    Resume StripIllErrExit

End Function

 

[Minty]

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.

 

[LGDGlen]

@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