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!

 

[Automatrix]

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!

I have had great fun with Power Query. But I can see that I made a typo in the Counts column, the text should say +-*, not +1*.

 

[ChristineJ]

I think I will use the code by 6StringJazzer, shown below, since I'll need to do this with VBA.

However, I'll need to run it as a macro rather than use a UDF. Is it possible to adapt this code so that it looks at the formula in P10 and returns the numerical result of the VBA in cell CS10? Thanks!

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

​

 

[6StringJazzer]

Not following why you can't use it as a UDF. You could call the function from CS10:

=SimpleExp(P10)

Otherwise just add this sub and call it.

Public Sub CountThem()

    Range("CS10").Value = SimpleExp(Range("P10").Value)

End Sub

 

[ChristineJ]

I'll be using this macro to evaluate formulas on other non-macro enabled workbooks.

The extra code you provided works perfectly for what I need. Thanks for sticking with me! I'm just learning VBA. C