Code to replace special character with space (1 Viewer)

[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

 

[Uncle Gizmo]

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:-

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table OP "avalve" asked this question on Access World Forums:- "Howdy! Let me make a small scenario. Let's say I have a table with fields CarMake/CarModel/Year/Color. What I am trying to do is to export the table to a single Excel Workbook with each CarMake having their...

www.niftyaccess.com

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

Sell vehicle parts | UK Seller Centre

With so many car and motorcycle parts available for a myriad of makes and models, buyers want to know for sure that they’re buying the right part for their vehicle.

www.ebay.co.uk

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"

 

[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.

 

[Uncle Gizmo]

Funnily enough, excel sheet renaming is one of its primary functions in lots of my apps.

Thanks again Minty, for pointing me in the direction of this code. Here it is working with a massive data set 45000 records & 286 groups (Makes of Car) which creates 286 individual pages in an Excel spreadsheet.

Make Hundreds of Excel Sheets From MS Access Table - Nifty Access

 

[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