Trying to help out in an Answers forum question regarding identifying if entries (strings) contained special characters or not
Find special characters in a part number
I need to filter a table on a field that has part numbers with special characters and pull only those values.
I went digging and updated an existing RegEx function I had from previous work that could do exactly this.
So, should you need to do so yourself, feel free to use the following
'---------------------------------------------------------------------------------------
' Procedure : RegEx_HasSpecialCharacters
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine if the passed string has special characters in it.
' Returns: True -> has special characters
' False -> alphanumeric only
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' Dependencies: RegExEscapeSpecialChars()
' -> https://www.devhut.net/vba-replace-string-between-delimiters/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput : String to validate
' sException: List of exception characters
'
' Usage:
' ~~~~~~
' RegEx_HasSpecialCharacters("Dani el")
' Returns -> True
' RegEx_HasSpecialCharacters("Dani el", " ")
' Returns -> False
' RegEx_HasSpecialCharacters("Dani%-el")
' Returns -> True
' RegEx_HasSpecialCharacters("Dani%-el", "%-")
' Returns -> False
' RegEx_HasSpecialCharacters("Daniel")
' Returns -> False
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2020-02-16 Initial Release
' 2 2022-03-23 Initial Public Release, Forum Help
' Added header block
' 3 2022-04-05 Added sExceptions input argument
'---------------------------------------------------------------------------------------
Public Function RegEx_HasSpecialCharacters(ByVal sInput As Variant, _
Optional sExceptions As String) As Boolean
On Error GoTo Error_Handler
Dim oRegEx As Object
If IsNull(sInput) = False Then
'Escape sExceptions String as required
If sExceptions <> "" Then sExceptions = RegExEscapeSpecialChars(sExceptions)
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "([^a-zA-Z0-9" & sExceptions & "])"
'Debug.Print .Pattern If you want to see the properly build pattern
.Global = True
.IgnoreCase = True
.MultiLine = True
RegEx_HasSpecialCharacters = .Test(sInput)
End With
End If
Error_Handler_Exit:
On Error Resume Next
If Not oRegEx Is Nothing Then Set oRegEx = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: RegEx_HasSpecialCharacters" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
Required Helper Function
Please be aware that this function is dependent on my RegExEscapeSpecialChars function which you can copy from
VBA - Replace String Between Delimiters
Here’s another question from an UtterAccess that I helped with, further perfected and thought might serve others. How can you replace all the occurrences of a string within another string? Now there are various approaches that can work, but I knew the most straightforward solution would be to employ the power of Regular Expressions again.
Also, should your back-end be something other than an Access file, you might be able to use RegEx directly in your queries bypassing the need to use any VBA function whatsoever.
Now Identifying such cases is one thing, but if you’re looking to cleanup such entries, you may like to review my previous article on the subject:
Use VBA RegEx to Clean a String
After working on yesterday’s post there was one aspect of my code that bothered me and I knew I could perfect, the line which removed certain Unicode characters vPropValue = Replace(Replace(Replace(Replace(vPropValue, ChrW(8236), ""), ChrW(8234), ""), ChrW(8207), ""), ChrW(8206), "") Don’t get me wrong, to date, I haven’t experienced any issues, but I haven’t been able…
