Extract number of data points from an equation...

[Sontauzo]

Hello all,

I have an odd inquiry. I have a spreadsheet that contains cells that have multiple data points in them. Most of them look like this 120+120+60+50. This gives me a great total, but I am wondering if there is a way to find out how many entries there are in each cell. For instance, in the example above, I am looking for a marco to return the number '4' since there are 4 entries in the cell. The tricky part is that some of the cells have multiplication formulas also. Example: 120*3+50+30*4. In this example I would want to know that there were '8' entries that made up that total.

Is such a thing even possible? The spreadsheet is large and i was hoping for a faster way of analyzing this information.

Thank you for your time and assistance,

BWL

 

[StephenCrump]

Most of them look like this 120+120+60+50. This gives me a great total ...

I am guessing you mean a formula: =120+120+60+50 rather than a string: 120+120+60+50

This should work either way, provided your formulae/strings are always as specified, e.g. no other characters such as brackets?

Function GetCount(InValue As Variant) As Long

    Dim s As String
    
    s = InValue
    On Error Resume Next
    s = InValue.Formula
    On Error GoTo 0
    
    GetCount = Len(s) - Len(Replace(Replace(s, "+", ""), "*", "")) + 1
    
End Function

I am also guessing that your 8 for the second example was a typo, and should have been 5?

 

[Sontauzo]

Thank you so much. This worked great!