counting the number of instances of a string within another string

[Keith R]

I'm sure there is an easy way to do this; I just haven't had to do it before
so I'm not sure what the most elegant solution is.

I have a column of values, and within each cell, there are an unknown number
of commas (e.g. "323,76,12,43"). I need to identify (for each cell, in the
next column over) how many commas are present. The basic instr or match type
functions (I think) only provide the first match location, not a total
number of matches. Maybe something like the opposite of REPT, only something
that ignores all the other text and just gives the count of the target
string that is already repeated in the cell.

Any suggestions on a preferred approach?

Thank you,
Keith

 

[Bob Phillips]

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

Try this:

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

Biff

 

[Keith R]

Excellent, thank you Bob and Biff!

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)