Counting items in a cell

[MacBookProUser]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,

I would like to create a formula to count the number of items in a cell. The items in the cell appear as follows:

23,11,1,0,0,4,0,7,0,3,0,2,0,0,0,0,0,0

My goal is to count the items separated by commas, so in the example above, the formula would return 18.

I would also like to be able to count the number of non-zero entries and perform other statistical analysis on these values.

Normally, I would have imported this data as CSV where each value is in it's own cell, however, the number of entries for a row exceeds Excel's column limit.

Any assistance is greatly appreciated.

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,

I would like to create a formula to count the number of items in a cell. The
items in the cell appear as follows:

23,11,1,0,0,4,0,7,0,3,0,2,0,0,0,0,0,0

My goal is to count the items separated by commas, so in the example above,
the formula would return 18.

I would also like to be able to count the number of non-zero entries and
perform other statistical analysis on these values.

Normally, I would have imported this data as CSV where each value is in it's
own cell, however, the number of entries for a row exceeds Excel's column
limit.

Any assistance is greatly appreciated.

Wait a minute here. Are you saying that you have more than 16,384 values in
a cell? A cell can only contain 32,768 characters. So you have 16K+ items
and therefore 16K commas? If so, it seems that it will fit and you can
indeed import the CSV file.

Or, you could actually count the commas in a cell with this formula. It
assumes that the data is in A1:

=SUM((MID(A1,COLUMN(1:1),1)=",")*1)

After entering placing this formula in the formula bar, press
command-shift-enter to array enter it. The formula will then appear
surrounded with brackets {}

 

[MacBookProUser]

Bob,

Many thanks for the answer. It worked.

I was also able to use the Convert Text to Columns command to place the data into individuals cells. My main challenge now is to find ways to improve the speed to open the file and processing of formulas.

Thanks again,

John

 

[Carl Witthoft]

Bob,

Many thanks for the answer. It worked.

I was also able to use the Convert Text to Columns command to place the data
into individuals cells. My main challenge now is to find ways to improve the
speed to open the file and processing of formulas.

Thanks again,

John

If you are consistently working with datasets that large, you really
will be better off with a math/stats application rather than Excel. It
will be well worth the learning curve.
My favorite (and free) is R (cran.r-project.org), and things like
MatLab, Octave, SciLab, Mathematica, etc. come to mind as well.