CODEWITHSUNDEEP
excelregexvba
Lynn
Lynn

Reputation: 3660

Delimiters in Excel VBA Regex Patterns, filter something but not others

I wrote to VBA function that removes number sequences between delimiters from a cell in Excel. The issue I am having is that if the string contains text between the delimiters '(' and '),' then from the first delimiter to the end of the string is deleted. The code I have below works fine on delimited numbers.

I need to change the Regex pattern so that if a letter or word is within the delimiter is found, don't do anything, move on to the next set of delimiters, and if there are numbers, remove the delimiters and everything between them.

Function RemoveTags(ByVal Value As String) As String
    Dim rx As New RegExp
    With rx
        .Global = True
        .Pattern = "\((.*\d)\)"
    End With
    RemoveTags = WorksheetFunction.Trim(rx.Replace(Value, ""))
End Function

This one is a good one.
Inputs: Put a stop to Rugby's foul school leader (5,2,3,4)
OutPut: Put a stop to Rugby's foul school leader

With the pattern: "\((.*\d)\)"
Input: Put a (stop to Rugby's) foul school leader (5,2,3,4)
Output: Put a
The above should be: Put a (stop to Rugby's) foul school leader

I have tried adding "\W" and other things to the pattern and have had no luck. I also have been using the regex expressions emulator at https://regexr.com/3hmb6

Thank you for your time and your help on this.

Upvotes: 2

Views: 119

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626738

Your code can be fixed using

Function RemoveTags(ByVal Value As String) As String
    Dim rx As New RegExp
    With rx
        .Global = True
        .Pattern = "\s*\(\d+(?:,\d+)*\)"
    End With
    RemoveTags = WorksheetFunction.Trim(rx.Replace(Value, ""))
End Function

See the regex demo.

Details:

  • \s* - zero or more whitespaces
  • \( - a ( char
  • \d+ - one or more digits
  • (?:,\d+)* - zero or more occurrences of a , + one or more digits sequences
  • \) - a ) char.

If there can be whitespaces between comma-separated numbers, use

.Pattern = "\s*\(\d+(?:\s*,\s*\d+)*\)"

and if there can be floats:

.Pattern = "\s*\(\d+(?:\.\d+)?(?:\s*,\s*\d+(?:\.\d+)?)*\)"

Upvotes: 3

JvdV
JvdV

Reputation: 75840

I believe the answer to the problem has been given through a comment, but for the sake of alternatives; If you do happen to work with ms365 you could avoid VBA and creating a UDF (at least in given sample I don't see nested paranthesis):

enter image description here

Formula in B1:

=MAP(A1:A5,LAMBDA(z,IFERROR(TRIM(CONCAT(TEXTSPLIT(z,LET(x,TEXTSPLIT(z,{"(",")"},,1),"("&TOROW(IF(-SUBSTITUTE(x,",",),x,),2)&")"),,1))),z)))

Upvotes: 3

Related Questions