Count the number of entries in a cell

  • Thread starter Thread starter alavan
  • Start date Start date
A

alavan

Hi,

I've got numbers manually entered in cells, i.e. =65+15+10.

I need to count the number of "numbers" in each cell. The result fo
the above would be "3". Otherwise, I have to manually look at each cel
and type the number of numbers I see.

Thank you,

Alava
 
Hi
this would be only possible using VBA. e.g. try the following:
Public Function Get_Formula(rng as range)
Get_Formula = rng.formula
end Function

Now use the following formula
=LEN(GET_FORMULA(A1))-LEN(SUBSTITUTE(GET_FORMULA(A1),"+",""))+1
 
Frank's code for Get_Formula is very useful by itself. But if you want to just
use one function, you could combine Frank's worksheet formula and his function
into one UDF:

Option Explicit
Public Function CountTermsInFormula(rng As Range)

Dim myStr As String
Set rng = rng(1)

CountTermsInFormula = 0
If rng.HasFormula Then
myStr = rng(1).Formula
myStr = Application.Substitute(myStr, "-", "+")

If InStr(1, myStr, "+", vbTextCompare) = 0 Then
'already 0, don't change it
Else
CountTermsInFormula = Len(myStr) _
- Len(Application.Substitute(myStr, "+", "")) + 1
End If
End If

End Function

(I looked for minuses, too. Delete that first application.substitute if you
don't want to count them.)
 
Back
Top