counting numbers in a cell

[mhsacks]

Is there a way to count the number of numbers in a cell?

so that a cell with 21, 22, 23, 24 would yield the
number 4.

I have excell 97.

 

[mhsack]

Thank you for the prompt reply. What would the formula be
if i did not use comas?
So that 20 21 23 24 would yield 4.
thankyou.

 

[Andy B]

Very similar:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Nice formula, by the way, Anon!! Good thinking.

Andy.

 

[mhsacks]

Dear Anon,

-----Original Message-----


If the numbers are always separated by a comma, the result you want is the
number of commas plus one.
So, for cell A1:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1


.