counting the number of instances of a string within another string
 

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

counting the number of instances of a string within another string
 

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

--
---
HTH

Bob

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



"Keith R" wrote in message
...
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

counting the number of instances of a string within another string
 

Try this:

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

Biff

"Keith R" wrote in message
...
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

counting the number of instances of a string within another string
 

Excellent, thank you Bob and Biff!


"Bob Phillips" wrote in message
...
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

--
---
HTH

Bob

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



"Keith R" wrote in message
...
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