Counting Formula

[Guest]

I was wondering if there was anyway to set up a formula to count the number of numbers in a cell that are separated by commas. For example, Cell A1 has a formula that will count Cell A2 that as the following (1,2,3,4,5,6,7,8,9,10). The result in Cell A1 should be 10 for the number of variables in the cell.

 

[Ken Wright]

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I was wondering if there was anyway to set up a formula to count the number of

numbers in a cell that are separated by commas. For example, Cell A1 has a
formula that will count Cell A2 that as the following (1,2,3,4,5,6,7,8,9,10).
The result in Cell A1 should be 10 for the number of variables in the cell.

 

[Dan E]

Steven,

This may do what your looking for:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

essentially it counts the number of commas "," and adds 1

Dan E

I was wondering if there was anyway to set up a formula to count the number of numbers in a cell that are separated by commas.

For example, Cell A1 has a formula that will count Cell A2 that as the following (1,2,3,4,5,6,7,8,9,10). The result in Cell A1
should be 10 for the number of variables in the cell.

 

[Guest]

I will give it a try. Thanks so much. This will cut my work time in half