Separating Strings of Text

[Ben in CA]

Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model numbers,
and the values are separated by commas and spaces in each cell. (eg. 8890,
x3340, mx750)

To make things more confusing, each model is colored differently depending
on the distributor, and there are several different colors in some cells. In
total, about six different colors are used (green, red, blue orange, gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben

 

[Pete_UK]

To get a count of the number of strings in each cell, you just need to
count the number of commas and add one. This formula will do that:

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

It assumes that your model numbers are in cell A1 - adjust to suit,
then you can copy down.

As for counting how many strings exist of each colour for a range, you
would need a macro to do that.

Hope this helps.

Pete

 

[Ben in CA]

Thanks for that Pete_UK!

Does anyone know macro would be required?

Ben

 

[Pete_UK]

You're welcome, Ben.

Can't help on the second part.

Pete

 

[Ben in CA]

Hi Pete,

The problem with this formula is that it only calculates commas + 1 in a
particular cell - I want to know how many strings in a range of cells. (Some
of the cells are blank, some contain only one value, some 12 values,
separated by commas and spaces.)

Any ideas anyone?

 

[Dave Peterson]

And if the cell is empty:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+(A1<>"")

 

[Dave Peterson]

This will give you the number of commas in a range:
=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,",","")))

This will give you the number of non-empty cells:
=counta(a1:a100)

So you can add them to get the total:
=counta(a1:a100)+SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"@","")))

Be aware that =counta() will count cells that contain spaces and even cells that
contain formulas that evaluate to ""

 

[Ben in CA]

I haven't got this figured out yet.

I'd be open to using a complex macro if required - if anyone has any ideas.

Thanks,

Ben

 

[Ben in CA]

Hi Steve,

That's phenomenal! The first part is exactly what I wanted.

As for the color counting - how could this be setup so that it lists how
many green, how many blue, how many red, etc.

So that I could put
=CountColorRed'Items'!(F4:M137)
=CountColorBlue'Items'!(F4:M137)
=CountColorGreen'Items'!(F4:M137)
=CountColorOrange'Items'!(F4:M137)
=CountColorYellow'Items'!(F4:M137)
=CountColorPurple'Items'!(F4:M137)
=CountColorGrey'Items'!(F4:M137)

Is there a way that these can be combined?

Thank you for your time!

Ben

 

[Ben in CA]

Thanks a lot Steve!

Ben

Ben,

It would be a bit more complex to count instances of different colors but
can be done. I'll see if I can find a little time to work on it in the next
couple of days.

Steve

 

[Ben in CA]

THANK YOU SO MUCH Steve!

Have a fantastic day!

Ben

 

[Ben in CA]

Hi Steve,

Just wanted to let you know that this works GREAT!

Some minor modifications I might need to make will be making sure it
"refreshes" the calculation after any changes in the range, and the addition
of a "Other" color script.

Thanks again for your help!

Have a great Christmas and New Years Steve!

Sincerely,

Ben