counting commas within a string

R

Ron.Winkley

Hi to All,

Cell A1 contains a text string such as 56,58,12,1,22,893
I want to determine how many numbers are contained within the string
Simply, counting the commas and adding 1 one seems to be the easy
answer
How can I create an Excel formula to count the commas, or please
suggest
an alternative method.

Thanks for your help,

Cheers,
RonW
 
H

Harald Staff

Hi RonW

Length of string minus length of sting without commas:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

HTH. Best wishes Harald
 
D

Dave Peterson

If you want to count the number of "elements" that are separated by commas:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+(A1<>"")
 
Top