I need to count occurr. of the character "," in a single cell

[orchid11652]

i need a formula to count the occurence of the comma character (",") in a
single cell. That's the easiest way I can think of to count dates that are
exported into a single excel file. I know I could use the text to column
function and use "," as the delimiter and then count the cells, but there are
a lot of dates and I might run out of columns.

 

[Teethless mama]

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

 

[Kevin Vaughn]

i need a formula to count the occurence of the comma character (",") in a
single cell. That's the easiest way I can think of to count dates that are
exported into a single excel file. I know I could use the text to column
function and use "," as the delimiter and then count the cells, but there are
a lot of dates and I might run out of columns.

Since you are trying to count the data that the commas separate, you
need to add 1 to the formula that Teethless Mama proposed. One way
would be with this formula:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",E1)) + ISNUMBER(SEARCH(",", A1))

 

[orchid11652]

Thank you. I'll try it.

Since you are trying to count the data that the commas separate, you
need to add 1 to the formula that Teethless Mama proposed. One way
would be with this formula:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",E1)) + ISNUMBER(SEARCH(",", A1))

 

[orchid11652]

Thank you - I'll try it out.

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

 

[Kevin Vaughn]

Since you are trying to count the data that the commas separate, you
need to add 1 to the formula that Teethless Mama proposed. One way
would be with this formula:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",E1)) + ISNUMBER(SEARCH(",", A1))

I realized on Saturday that this is incorrect where there is only one
item in A1. More appropriate would have been something like this:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",E1)) + (A1<>"")
Sorry about that, Chief!