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

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.

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

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


"orchid11652" wrote:

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.

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

On Mar 21, 2:47 pm, orchid11652
wrote:
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))

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

Thank you. I'll try it.

"Kevin Vaughn" wrote:

On Mar 21, 2:47 pm, orchid11652
wrote:
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))

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

Thank you - I'll try it out.

"Teethless mama" wrote:

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


"orchid11652" wrote:

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.

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

On Mar 22, 2:54 pm, "Kevin Vaughn" wrote:
On Mar 21, 2:47 pm, orchid11652

wrote:
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))

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!