Regex to add brackets

iantech

New Member
Joined
Feb 13, 2020
Messages
8
Office Version
  1. 2013
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,034
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Will any of your values ever have the subtraction (-) or division (/) symbol in them?
 

iantech

New Member
Joined
Feb 13, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,528
Office Version
  1. 365
Platform
  1. Windows
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..)
 

iantech

New Member
Joined
Feb 13, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,528
Office Version
  1. 365
Platform
  1. Windows
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:

iantech

New Member
Joined
Feb 13, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,528
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

iantech

New Member
Joined
Feb 13, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,175,859
Messages
5,899,904
Members
434,805
Latest member
Nihon

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
Top