Regex to add brackets

iantech

New Member
Joined
Feb 13, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
'Hi I'd appreciate any help from someone really good at regex stuff with coming up with a solution, to add parentheses to parts of an expression.

The parts that need parentheses are any multiplied expressions that are NOT already in parentheses. For example i need to change :
a*b +c+ d*e*f + g
to
(a*b)+c+( d*e*f)+ g

another example .. change:
(a1+b1) + ( c1*d1) + e1*f1*g1*h1 + J1
to
(a1+b1) + ( c1*d1) + (e1*f1*g1*h1) + J1

Thanks in advance
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Will any of your values ever have the subtraction (-) or division (/) symbol in them?
 
Upvote 0
No the only elements will be * + ( ) and variable names with no spaces like a1 f1 p3 etc.
Messing around with some other stuff i was able to extract multiplied expressions that have brackets, but i want the opposite of that.
This is what i have so far :

VBA Code:
Function testParen()

Dim cText As String, cPattern As String
Dim itemFound As Variant, nItems
Dim getParen As New RegExp

getParen.Global = True
getParen.IgnoreCase = True

cText = "(a1*q1) + (b1*c1) + d1*e1*f1 * (g1+h1) + (k1*m1) "
cPattern = "(^|[^*])\(([^()]+)\)"
getParen.Pattern = cPattern

cText = Replace(cText, " ", "")

Dim mc As MatchCollection

Set mc = getParen.Execute(cText)
nItems = mc.Count

'Debug.Print vbNewLine
If nItems > 0 Then
  Debug.Print nItems
  For Each itemFound In mc
     Debug.Print Replace(itemFound, "+", "")
  Next
Else
  Debug.Print "No items"
End If

End Function
 
Upvote 0
another example .. change:
(a1+b1) + ( c1*d1) + e1*f1*g1*h1 + J1
Since this would make it more difficult, just checking that this is a valid example with a space between the bracket and the c1?
If that is a valid example then can there also be
- a space before the closing bracket? eg (a1+b1 )
- multiple spaces in those positions eg (where "." represents a space character) (....a1+b1..)
 
Upvote 0
Since this would make it more difficult, just checking that this is a valid example with a space between the bracket and the c1?
If that is a valid example then can there also be
- a space before the closing bracket? eg (a1+b1 )
- multiple spaces in those positions eg (where "." represents a space character) (....a1+b1..)
Actually , to make it simpler , all spaces are replaced with a null string at the start of the function , so the regex engine won't have to worry about those.
 
Upvote 0
See if this is any use then. It includes the replacement of spaces.
Of course it could be written as a function if you want.

VBA Code:
Sub AddBrackets()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Global = True
  RX.Pattern = "([^\(]|^)([a-z]+\d*\*[^+]+)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(Replace(a(i, 1), " ", ""), "$1($2)")
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

My sample data and results

iantech.xlsm
AB
1DataResults
2a*b +c+ d*e*f + g(a*b)+c+(d*e*f)+g
3(a1+b1) + (c1*d1) + e1*f1*g1*h1 + J0(a1+b1)+(c1*d1)+(e1*f1*g1*h1)+J0
4(a1+b1) + ( c1*d1) + e1*f1*g1*h1 + J1(a1+b1)+(c1*d1)+(e1*f1*g1*h1)+J1
5a1*bg2*h707*k99(a1*bg2*h707*k99)
6a1+b2+c3+d4a1+b2+c3+d4
Sheet1
 
Last edited:
Upvote 0
See if this is any use then. It includes the replacement of spaces.
Of course it could be written as a function if you want.

VBA Code:
Sub AddBrackets()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Global = True
  RX.Pattern = "([^\(]|^)([a-z]+\d*\*[^+]+)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(Replace(a(i, 1), " ", ""), "$1($2)")
    Next i
    .Offset(, 1).Value = a
  End With
End Sub


Hey, tis is almost working. Thanks

i guess i didn't give a sufficiently generalized example, but the function seems to fall short when one of the multiplicands is itself compound.
Let me give an example. If i can get this one solved it would be perfect.

a*b * (c + D)+ e*f*g*(h+j)*k should give me

(a*b)*(c+D) + (e*f*g)*(h+j)*k
in other words, bracketed expressions shouldn't be bracketed

your function gives
(a*b*(c)+D)+(e*f*g*(h)+j)*k

which is not correct
 
Upvote 0
i guess i didn't give a sufficiently generalized example,
It is important to know all the possibilities. ;)

This seems to cover that one extra example.

VBA Code:
Sub AddBrackets_v2()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Global = True
  RX.Pattern = "([^\(]|^)([a-z]+\d*\*[^+(]+)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = Replace(RX.Replace(Replace(a(i, 1), " ", ""), "$1($2)"), "*)", ")*")
    Next i
    .Offset(, 1).Value = a
  End With
End Sub
 
Upvote 0
Solution
It is important to know all the possibilities. ;)

This seems to cover that one extra example.

VBA Code:
Sub AddBrackets_v2()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Global = True
  RX.Pattern = "([^\(]|^)([a-z]+\d*\*[^+(]+)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = Replace(RX.Replace(Replace(a(i, 1), " ", ""), "$1($2)"), "*)", ")*")
    Next i
    .Offset(, 1).Value = a
  End With
End Sub
This is fantastic. Thanks a million for this. I'm learning regex slowly, and hopefully with some more practice, i'll be able to figure out what you did.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top