Formula to evaluate content with parentheses

[ChristineJ]

Is there a formula to evaluate the expression below to count the number of times content within parentheses does NOT contain one of the following:
plus sign (+), minus sign (-), multiplication sign (*), division sign (/), power sign (^), colon ), comma (,)

=((A1))+SUM(P4:Q4)-(A1-99)+(B1^J17)*(C1)/(D1)-SUMPRODUCT(L5:L9,M5:M9)+(A17+66/K22)+(S4)-(77)

There are 10 items within parenthesis in this expression. Of these, 5 of them do not have the signs or characters listed above. The answer is 5.

Thanks!

 

[Special-K99]

So really you're trying to count (and presumably ultimately remove?) the unneeded parentheses?
((A1))
(C1)
(D1)
(S4)
(77)

I can't see an easy way to do this.
You've asked for a formula. But because of the complexity of what your original formulas could be, such as the one above, I suspect VBA may be your only way.
Even then, as I said, it wouldn't be easy (unless I'm missing something obvious).

 

[KRice]

Here is one idea:

Book1
	A	B
2	Expression:	#NUM!
3		=((A1))+SUM(Q4:R4)-(A1-99)+(B1^K17)*(C1)/(E1)-SUMPRODUCT(M5:M9,N5:N9)+(A17+66/L22)+(T4)-(77)
4		There are a total of 10 items in parentheses. Of these, 5 do not have the characters, and 5 of them do.
Sheet1

Cell Formulas
Range	Formula
B2	B2	=((A1))+SUM(Q4:R4)-(A1-99)+(B1^K17)*(C1)/(E1)-SUMPRODUCT(M5:M9,N5:N9)+(A17+66/L22)+(T4)-(77)
B3	B3	=FORMULATEXT(B2)
B4	B4	=LET(expr,FORMULATEXT(B2),esa,IFERROR(TEXTSPLIT(expr,"("),""),esb,TRANSPOSE(IFERROR(TEXTSPLIT(esa,")"),"")),cntpe,COUNTA(esb)-2,charsrch,--ISNUMBER(SEARCH({"+","-","~*","/","^",":",","},DROP(esb,2))),colcnt,COLUMNS(charsrch),tally,SUM(--(MMULT(charsrch,SEQUENCE(F1,,1,0))>0)),TEXTJOIN(" ",,"There are a total of",cntpe,"items in parentheses. Of these,",cntpe-tally,"do not have the characters, and",tally,"of them do."))

 

[6StringJazzer]

I cannot imagine how this could be done with a formula, but someone may have an inventive approach that I cannot imagine.

I would do this with VBA and regex.

This solution may not correctly count all possible expressions with nested parentheses but works for your example.

Public Function SimpleExp(Expression As String) As Long

   Const Pattern = "\([^),:/+^*-]+\)"
   Dim RE As Object
   Dim MatchList As Object
 
   Set RE = CreateObject("vbscript.regexp")
 
   RE.Pattern = Pattern
   RE.IgnoreCase = True
   RE.Global = True
 
   Set MatchList = RE.Execute(Expression)
 
   SimpleExp = MatchList.Count

End Function

 

[KRice]

"...may not correctly count all possible expressions with nested parentheses but works for your example"

@6StringJazzer, you make a good point. My formula-based approach that relies on splitting the text string at the parentheses works for the example, but for more complex nesting, I believe it will fail.

@ChristineJ, could you also clarify: Where you've written "...does NOT contain one of the following...", does that mean "...does not contain any of the following...". In other words, you do not care if a parenthetical expression contains two plus (+) signs, or two of more of the characters (e.g., a plus sign and minus sign) ...such an expression would simply be counted as containing a targeted character?

 

[shinigamilight]

"\(\w+\)"

This regex pattern might help you.

 

[Automatrix]

Power Query solution:

let
    Source = "((A1))+SUM(P4:Q4)-(A1-99)+(B1^J17)*(C1)/(D1)-SUMPRODUCT(L5:L9,M5:M9)+(A17+66/K22)+(S4)-(77)",
    #"Converted to Table" = #table(1, {{Source}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Converted to Table", {{"Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Column1] <> "")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Repeat("(", Text.Length([Column1]) - Text.Length(Text.Replace([Column1], ")", ""))) & [Column1]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Added Custom", {{"Custom", each Text.BeforeDelimiter(_, ")", {0, RelativePosition.FromEnd}), type text}}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Text Before Delimiter", "Custom.1", each [Custom] & ")"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Counts", each if List.Count(Splitter.SplitTextByAnyDelimiter({"+", "-", "*", "/", "^", ":", ","})([Custom.1])) > 1 then "Contains +1*/^:," else "Doesn't contain +1*/^:,")
in
    #"Added Custom1"

Result, which can be consolidated further into the number 5 with Group By.

 

[ChristineJ]

Many thanks for the responses. I have learned so much just from reading them.

My goal was just to identify if there are any single cell references unnecessarily wrapped in parenthesis in a formula. The formulas I'll be evaluating are relatively straightforward without a whole lot of complex nesting.

6StringJazzer - your function seems to be working just fine. I've tested it on a number of formulas and it has been returning the right amount.

KRice - I'm working now on implementing your formula to test it out. It is amazing you can create such an impressive solution...well beyond what I would be able to create. I'm stepping through it and will let you know once I have it up and running.

Same with your Power Query solution, Automatrix. This is new territory for me but I will implement and will post when it is working!

Appreciate all this great advice!

 

[Rick Rothstein]

Assuming the formula you want to examine is in cell B2, this formula will return the number of items in parentheses that do NOT contain a "+", "-", "*", "/", "^", ":" or a ","...

=SUM(0+(LET(a,TOROW(INDEX(TEXTSPLIT(TRIM(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2),"("," "),")"," "))," "),,SEQUENCE(,99,2,2)),3),REDUCE(0,a,LAMBDA(c,x,c+ISERROR(LEN(TEXTBEFORE(a,{"+","-","*","/","^",":",","}))))))>0))

NOTE: As written, this formula assumes there will be no more than 45 items in parentheses, if there could be more, then increase the 99 (add 2 for each possible item in parentheses).

 

[ChristineJ]

Amazing, Rick Rothstein. Works well as a formula! Thanks.

​