Count number of amounts in one formula in one cell

[gillic]

Hi!
Does anyone know if there is a formula for counting the number of amounts between +/-/* in one formula in one cell? If I have a cell with a long, but simple calculation as:
=199+25+989+52,5-3849+2658+6598-1,22 (and hundreds more)
I'd like in the next cell to have the number of sums, here it would be 8.

Probably a dumb question, but would still be very useful to me if someone could help

 

[Dim Me as xlNoob]

Hi gillic welcome to the forum.

Here is a user defined function that will count the number of operands in the target formula.

Private Function countOPERANDS(ByVal rngSUM As Range) As Long
    Dim strSUM As String
    
    strSUM = rngSUM.Formula
    strSUM = Replace(strSUM, "+", "|")
    strSUM = Replace(strSUM, "-", "|")
    strSUM = Replace(strSUM, "*", "|")
    strSUM = Replace(strSUM, "/", "|")
    countOPERANDS = Len(strSUM) - Len(Replace(strSUM, "|", "")) + 1
End Function

To use a user defined function:
1. From Excel, open the visual basic editor with Alt + F11.
2. From the menu bar, select Insert > Module.
3. Paste the code into the new module.
4. You may now close the visual basic editor.

The function can now be used just like any other functions in that workbook by typing "=countOPERANDS(A1)" where A1 is the cell in which you want to count the number of operands in the formula.

 

[JLGWhiz]

Hi!
Does anyone know if there is a formula for counting the number of amounts between +/-/* in one formula in one cell? If I have a cell with a long, but simple calculation as:
=199+25+989+52,5-3849+2658+6598-1,22 (and hundreds more)
I'd like in the next cell to have the number of sums, here it would be 8.

Probably a dumb question, but would still be very useful to me if someone could help

If you mean an Excel pre-defined function, then the answer is no. It might could be done with vba or vbs by looping and counting the ASCII charaters for the math operands. But I am not going to go there.

Looks like @Dim Me as xlNoob has already got the solution you need. Forgot that method.

 

[Eric W]

Welcome to the board.

If all you have are {+,-,*,/} , and you have Excel 2013 or newer, then this might work for you:

=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"+",""),"-",""),"*",""),"/",""))+1

FORMULATEXT came in 2013, if you have something older, you might need VBA to look at it.

 

[Dim Me as xlNoob]

Welcome to the board.

If all you have are {+,-,*,/} , and you have Excel 2013 or newer, then this might work for you:

=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"+",""),"-",""),"*",""),"/",""))+1

FORMULATEXT came in 2013, if you have something older, you might need VBA to look at it.

I was scratching my head trying to figure out how to grab the formula from a cell as a string instead of the value. I'm using 2010 so that explains why!

 

[Eric W]

That's exactly why! Glad you posted the VBA equivalent though.

 

[FDibbins]

Try this...
=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"+",""),"-",""))+1