Delimiters in Excel VBA Regex Patterns filter something but not others

0 votes

I created a VBA programme that clears Excel cells of number sequences between delimiters. The problem I'm encountering is that the text from the first delimiter to the end of the string is erased if the string contains content between the delimiters "(" and "," On delimited numbers, the code I've provided below works perfectly.

I need to alter the Regex pattern so that if a letter or word is discovered within a delimiter, nothing should be done and the delimiters should be removed. If there are numbers, the delimiters and everything in between them should be removed.

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

Jan 12 in Others by Kithuzzz
• 28,900 points
61 views

1 answer to this question.

0 votes

Change your code to this:

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
answered Jan 12 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

How do I make my subs() not change inputdata in Vba excel

Use the ByVal keyword to pass on ...READ MORE

answered 3 days ago in Others by narikkadan
• 53,520 points
34 views
0 votes
1 answer

Language independent way to get "My Documents" folder in VBA Excel 2003

 Hello :)  This code may help you in your ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
153 views
0 votes
1 answer

C# code works in Visual Studio 2019 but not in online compiler

Repl.it compiles in Mono C# 4.6.2.0, according ...READ MORE

answered May 30, 2022 in Others by rajiv
• 1,620 points
227 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
177 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,520 points
295 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,520 points
200 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 53,520 points
281 views
0 votes
1 answer

Text with a dash in the cell but not in the formula bar (Excel)

What you have got here is called ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 53,520 points
98 views
0 votes
1 answer

Excel VBA userform paste text in expanded format (not just to one cell)

It should work if you simply supply ...READ MORE

answered Jan 21 in Others by narikkadan
• 53,520 points
41 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP