Counting numbers within a cell separated by comma

**hactic** · 07-14-2011, 11:56 PM

Hi, How can we count numbers in a cell that are separated by commas?

Eg., 12,13,14,15,[16]

The count result would be 4 in this case, excluding the one in the brackets.

Thanks.

**Azam Ali** · 07-15-2011, 12:49 AM

hi hactic

let your text is in cell # A1. try the follwing

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

=SUMPRODUCT(--LEN($A$1))-SUMPRODUCT(--LEN(SUBSTITUTE($A$1,",","")))

**hactic** · 07-15-2011, 01:26 AM

Hi thanks. I tried this but if you remove the brackets say from 12,13,14,[15] to 12,13,14,15, the result still remains 3 instead of incrementing to 4. Any fix for that?

**Azam Ali** · 07-15-2011, 01:51 AM

Hi hactic

i am not clear what do u want. tell me what result u need in the follwing cases

12,13,14,[15]
12,13,14,15,
12,13,14,15

**hactic** · 07-15-2011, 09:05 PM

Originally Posted by Azam Ali

Hi hactic

i am not clear what do u want. tell me what result u need in the following cases

12,13,14,[15]
12,13,14,15,
12,13,14,15

i want to count numbers that are separated by commas. so 12,13,14,15 = 4; 12,13,14,[15] = 3; 12,13,14,[15],16 = 4

hope thats clear enough.

**Haseeb Avarakkan** · 07-15-2011, 09:37 PM

Hello,

Try this Array Formula. Confirmed with CONTROL+SHIFT+NTER, rather than just ENTER.

=COUNT(--TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),100*ROW($A$1:$A$100)-99,100)))