regex pattern not working in VBA

[johnmerlino]

Hey all,

in this snippet of code:

Set has_parens_with_content = CreateObject("VBScript.RegExp")

    With has_parens_with_content
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "\([A-Za-z0-9]\)"

 If has_parens_with_content.test(clean_string) Then
        clean_string = has_parens_with_content.Replace(clean_string, " ")
    End If

if at any time the current cell as alphanumeric characters in parenthesis, I want to remove3 those alphanumeric characters and the parenthesis. For example, this:

abc & abcd (abc)

to this:

abc & abcd

However, when I run the above code, it does not remove the parens with alpha characters. Im pretty sure the regex is right.

thanks for response

 

[xenou]

Not sure but try:
.Pattern = "\([A-Za-z0-9]+\)"

The character class only matches a single character - you want to match one or more such characters.

 

[RSXchin]

Xenou, not sure that it matters, but where is his end with?

 

[xenou]

^^ The OP reports a problem with the regex matching and not a syntax error compiling the code - so let us assume for now that End With is present in the code, though not shown. If not, the OP will tell us shortly. That's my take.

ξ

 

[RSXchin]

well, if that's the case... I see two things that can be replaced with a "."

 

[Rick Rothstein]

If there is at maximum one item in parentheses (there could be none), and if there is never an unmatched parenthesis, then you do not need RegExp to do your removal...

  Dim Parts() As String
  .....
  .....
  clean_string = "(ABC) dsfsdak sdf"
  Parts = Split(Replace(clean_string & "(", ")", "("), "(")
  Parts(1) = ""
  clean_string = WorksheetFunction.Trim(Replace(Join(Parts, ""), "((", ""))

 

[Scott Huish]

If you are removing it, should replace them with "", not " "

Function RemoveText(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "\([A-Z0-9]+\)"
    .IgnoreCase = True
    .Global = True
    RemoveText = .Replace(r, "")
End With
End Function


Sub test()
Dim s As String
s = "abc & abcd (abc) & abc (abdcedef)"
MsgBox RemoveText(s)
End Sub

 

[pgc01]

If there is at maximum one item in parentheses

This should not be the case, since .Global is set to True, but it's a good question to ask the OP.

 

[johnmerlino]

Not sure but try:
.Pattern = "\([A-Za-z0-9]+\)"

The character class only matches a single character - you want to match one or more such characters.

it worked. thanks.