Parenthesis Balancing Tool

[mlthornton]

I'm starting a UDF that will take an operation input that contains multiple inputs (not of Excel or VBA format), for example:

(A OR (B AND C) AND (A OR B))

For the longer ones, keeping the parenthesis quickly get hard to follow. My thinking is that you would put the string in above and the UDF would return:

(1A OR (2B AND C)2 AND (3A OR B)3)1

So that balancing parenthesis is easier. The general idea would be:

1. Find first ")"
2. Rename it as ")1" (first iteration, n = 1)
3. Now going right to left, find first "(" and rename it as "(1"
4. Delete both ")1" and "(1"
5. Repeat step 1, finding the new first ")" since the last set was deleted, and this time it will be ")2"

First does such a tool already exist? If not, here's where I am so far. Any thoughts here? Is there a way to force the For loop to search from right to left instead of left to right?

Function testing(UserInput As Range) As String

Dim Counter As Integer
Dim CurrentCharacter As String
Dim NewCounter As Integer
Dim NewString As String

For Counter = 1 To Len(UserInput)
NewCounter = 0
CurrentCharacter = Mid(UserInput, Counter, 1)
If CurrentCharacter = ")" Then
NewCounter = NewCounter + 1
NewCharacter = ")" & NewCounter
MsgBox NewCharacter
Exit For
End If
Next Counter


testing = UserInput.Value

End Function

 

[rlv01]

If you are trying to find mismatched parenthesis, pasting the equation into a programming editor like notepad++ , which has a feature to highlight matching brackets might be a better solution.

 

[mlthornton]

This is even better. Thank you for this very helpful tip

 

[kevin9999]

Just for fun, I thought I'd look at this as a coding problem. The trouble with a UDF is that you won't get the subscript on the numbers you're looking for. If a sub will do, select the cell with the string, and run the following code. As I said, just for fun ?
The XL2BB add in doesn't show the subscript, but I assure you, it's there on the numbers.

Sub Balance_Parentheses()
    Dim c As Range, i As Long, j As Long, k As Long
    Set c = ActiveCell
    If c = "" Then Exit Sub
   
    j = 1
    For i = 1 To Len(c)
    If c.Characters(i, 1).Text = "(" Then
        c.Characters(i, 1).Text = "(" & j
        j = j + 1
    End If
    Next i
   
    k = Len(c) + 1
    For i = 1 To k
    If c.Characters(i, 1).Text = "(" Then
        j = Mid(c, i + 1, 1)
        Else
        If c.Characters(i, 1).Text = ")" Then
            c.Characters(i, 1).Text = ")" & j
            k = k + 1
            j = j - 1
        End If
    End If
    Next i
   
    If Right(c, 1) <> 1 Then c = c & 1
    For i = 1 To Len(c)
        If IsNumeric(Mid(c, i, 1)) Then
            c.Characters(i, 1).Font.Subscript = True
        End If
    Next i
End Sub

sort.xlsb
	A	B	C
1	Before		After
2	(A OR (A AND C))		(1A OR (2A AND C)2)1
3
4	(A OR (B AND C) AND (A AND B))		(1A OR (2B AND C)2 AND (3A AND B)3)1
5
6	(A OR(B OR (A AND C)) AND (A OR B))		(1A OR(2B OR (3A AND C)3)2 AND (4A OR B)4)1
7
Sheet2

 

[ShowGo]

Hey there Kevin, thanks for posting this 'just for fun' project... it's really useful!

I am, however getting some odd results and I think it's because I have parenthesis in my column headers in my table.

That aside, I have tried to modify your code to no avail to do the following two tasks...

(1) - I thought it would be useful to add a count of how many "(" and how many ")" were present.​

(2) - Do you know how to undo the results so the cell's value doesn't end up with all the annotations?​

- I've added this msgbox to show the results generated from your code, so if the original value could Undo then it would be easier to go back in and edit.​

MsgBox "Completed. Here is the result: " & Chr(13) & _​

C & " <END>", vbOKOnly, "Function Completed: ('Balance_Parentheses')"​

​

BTW, this is my final cell value, which has the correct number of parenthesis, if you want to add another wrench into your code.

=IF([@[AMT COUNTER]] <> "", IF([@[AMT COUNTER]] <> "", ([@[TOTAL BALANCE]]+[@[AMT COUNTER]])*-1, 0), IF([@[AMT (OFFER)]] <> "", ([@[TOTAL BALANCE]]+[@[AMT (OFFER)]])*-1, 0))​

​

And, this is the Result: https://bit.ly/3O0DlRY​

 

[kevin9999]

Sorry, but I lost interest in this when I got zero response from the OP 7 months ago. Suggest you start a new thread

 

[ShowGo]

Sorry, but I lost interest in this when I got zero response from the OP 7 months ago. Suggest you start a new thread

I don't blame you, it gets pretty in the weeds.
Definitely want to thank you for making the effort to even respond. And, what you created already is a great start and helpful as-is - I appreciate your work.

 

[johnnyL]

I agree with Kelvin

 

[kevin9999]

I agree with Kelvin

You just couldn't wait could you

 

[johnnyL]