Regex with "[^a-zA-Z0-9'()""-._]" not replacing certain keys: #$%

[hellothere4]

Hi, I'm using a recommended character replace regex I got from this forum, however it is not fully working in deleting unspecified characters - wondering if anyone can figure out why.

Original String:acAc10[]() _~!#$%^'&*()+-[]`"

Actual Result:acAc10()_#$%'&*()+-"

Desired Result:acAc10() _!'-"



Function ClearString1(ByVal sWord As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^a-zA-Z0-9'()""-._]"
.Global = True
ClearString1 = .replace(sWord, "")
End With
End Function

 

[brettdj]

One issue is that you need to "escape" the "." in the pattern so that the pattern explicitly matches a ".", else "." is a wildcard

It is escaped like so
\.

so

.Pattern = "[^a-zA-Z0-9'()""-\._]"
[code]

The pattern on a whole though doesn't appear to be exactly want you want

What rules are you looking for?

Cheers

Dave

 

[Juan Pablo González]

I think you need to escape the - in there as well, and include the !. I'm not sure why in the expected result you have the first pair of parenthesis, but not the second.

Here's the rule that I was using:

.Pattern = "[^a-zA-Z0-9'()""\-\._!\s]"
[code]

Btw, you can "test" regex easily in this page:

http://www.gskinner.com/RegExr/

 

[brettdj]

I'm not sure why in the expected result you have the first pair of parenthesis, but not the second.

Yes, one of the issues that made me wonder about that pattern. The current pattern will also pass any alphanumeric character regardless of position

ie any "a" in
acAaaaaaaaaaac10[]() _aaaaaaaaaaaaa~!#$%^'aaaa&*()+-[]`
wouldd be retained

To return the first part of your desired result, ie
Original String:acAc10[]() _~!#$%^'&*()+-[]`"
Desired Result:acAc10()

I think would be more robsutly served by executing an exact match something like
"([a-z]{2}[A-Z]{1}[a-z]{1}\d{2})(.*?)(\(\))"
to match this part exactly
acAc10[]()
then discarding the submatch wildcard non-match of []
see sample below

It isn't clear to me how the last part of the string
!'-"
should be kept

Further detail would help

Sub test()
    MsgBox ClearString1("acAc10[]() _~!#$%^'&*()+-[]")
End Sub

Function ClearString1(ByVal sWord As String) As String
    Dim regM
    With CreateObject("VBScript.RegExp")
        .Pattern = "([a-z]{2}[A-Z]{1}[a-z]{1}\d{2})(.*?)(\(\))"
        .Global = True
        If .test(sWord) Then
            Set regM = .Execute(sWord)
            ClearString1 = regM(0).submatches(0) & regM(0).submatches(2)
        Else
            ClearString1 = "No Match"
        End If
    End With
End Function

 

[pgc01]

Hi

Remark: you don't need to escape the dot inside a character class. "[a.]" matches "a" or a dot, the dot is not interpreted as a metacharacter inside the character class.

Hi, I'm using a recommended character replace regex I got from this forum, however it is not fully working in deleting unspecified characters - wondering if anyone can figure out why.

Original String:acAc10[]() _~!#$%^'&*()+-[]`"

Actual Result:acAc10()_#$%'&*()+-"

Desired Result:acAc10() _!'-"



Function ClearString1(ByVal sWord As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^a-zA-Z0-9'()""-._]"
.Global = True
ClearString1 = .replace(sWord, "")
End With
End Function

Hi

You should continue in the original thread.

BTW, if it is the thread I'm thinking of, no one posted the pattern you are posting here. It seems you wrote or adapted this pattern yourself?

 

[brettdj]

Remark: you don't need to escape the dot inside a character class. f?

Quite right.

If the original thread was this one
http://www.mrexcel.com/forum/showthread.php?t=548924
then that appraoch was to delete any non-matching characters so the not approach [^] was appropriate

In this new question you appear to want to keep parentheses in one area and delete in another, which is why an extraction (Execute) may possibly be better than a Replace.

Have you moved the scope of your problem?

Cheers

Dave