Count values between commas
 

I need to be able to count how many entries are separated by commas. For
instance, in cell A2, I have:
AB1, AB2, AB3, AB4.
In cell B2, I want to display the number 4 because there are 4 values.

So if I have
AXX2, Ax3, A3TU0
In cell B2, I want to display the number 3 because there are 3 values.

Is there a way to do this?

Thanks in advance.

Count values between commas
 

Try something like this:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"jhicsupt" wrote:

I need to be able to count how many entries are separated by commas. For
instance, in cell A2, I have:
AB1, AB2, AB3, AB4.
In cell B2, I want to display the number 4 because there are 4 values.

So if I have
AXX2, Ax3, A3TU0
In cell B2, I want to display the number 3 because there are 3 values.

Is there a way to do this?

Thanks in advance.

Count values between commas
 

B2 should be A2 ?


"Ron Coderre" schreef in bericht
...
Try something like this:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"jhicsupt" wrote:

I need to be able to count how many entries are separated by commas. For
instance, in cell A2, I have:
AB1, AB2, AB3, AB4.
In cell B2, I want to display the number 4 because there are 4 values.

So if I have
AXX2, Ax3, A3TU0
In cell B2, I want to display the number 3 because there are 3 values.

Is there a way to do this?

Thanks in advance.

Count values between commas
 

Yes....In my testing I evidently transposed A2 and B2.

Thanks for catching that.


***********
Regards,
Ron

XL2002, WinXP


"Jack Sons" wrote:

B2 should be A2 ?


"Ron Coderre" schreef in bericht
...
Try something like this:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"jhicsupt" wrote:

I need to be able to count how many entries are separated by commas. For
instance, in cell A2, I have:
AB1, AB2, AB3, AB4.
In cell B2, I want to display the number 4 because there are 4 values.

So if I have
AXX2, Ax3, A3TU0
In cell B2, I want to display the number 3 because there are 3 values.

Is there a way to do this?

Thanks in advance.

Count values between commas
 

A minor variation of Ron's suggestion:

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+(A2<"")

It may handle empty cells better.

jhicsupt wrote:

I need to be able to count how many entries are separated by commas. For
instance, in cell A2, I have:
AB1, AB2, AB3, AB4.
In cell B2, I want to display the number 4 because there are 4 values.

So if I have
AXX2, Ax3, A3TU0
In cell B2, I want to display the number 3 because there are 3 values.

Is there a way to do this?

Thanks in advance.

--

Dave Peterson