Remove punctuation using RegEx

[Dr. Demento]

I'm looking for some assistance in a UDF to remove punctuation. Since I'm keeping diacritics, I can't use the usual [^A-Za-a0-9 ]. Looking at ASCII, I came up with the UDF below, but it doesn't work (at all).

Thoughts?? Thanks y'all.

Function remove_punctII(rts As String) As String

  With CreateObject("vbscript.regexp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "^[\x21-\x2F\x3A-\x40\x5B-\x60\x7B-\x7E]"
    
    remove_punctII = CStr(.Replace(rts, vbNullString))
  End With

End Function

 

[Rick Rothstein]

Can you list for us EITHER the characters you want to allow OR the characters you want to disallow?

 

[Dr. Demento]

Rick,

I was looking to remove the following:
!“#$%&‘()*+,–./:;<=>?@[\]^_`{|}~
Hex codes from: https://access-excel.tips/excel-vba-asc-function-get-ascii-value-text/

However, I think I figured it out (lots of trial/error), by removing the ^

.Pattern = "[\x21-\x2F\x3A-\x40\x5B-\x60\x7B-\x7E]"

However, if there's a more efficient way, I'd be happy to learn.

Thanks much.

 

[Rick Rothstein]

Rick,

I was looking to remove the following:
!“#$%&‘()*+,–./:;<=>?@[\]^_`{|}~
Hex codes from: https://access-excel.tips/excel-vba-asc-function-get-ascii-value-text/

First off, while I don't do Regular Expressions any more (last time I touched them was the late 1990's), I am pretty sure you can just put those characters between the squared brackets directly (doubling up the quote mark because it is located in a text constant defined by quotes) instead of playing with Hex values. With that said, here is a way to do what you want without using Regular Expressions...

[table="width: 500"]
[tr]
	[td]Function RemovePunct(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If InStr("!""#$%&‘()*+,–./:;<=>?@[\]^_`{|}~", Mid(S, X, 1)) Then Mid(S, X) = Chr(1)
  Next
  RemovePunct = Replace(S, Chr(1), "")
End Function[/td]
[/tr]
[/table]