Counting Words?

Hi There,
Does anyone knows the formula that is capable of counting words?
The trick is that words are separated by coma (see example below)

Example:

Box,Cable,Cup
Plate,Cup,Cable
Key,Box
Ring,Box,Cable
Pen,Box,Key

And so on...

I need to find the way of calculating each word so would appreciate if someone knows how to do it.

Best Regards
Dan

Try this:

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

assuming your string is in A1.

Hope this helps.

Pete

Thank You but this formula will only calculate number of words separated with comma right?
I need to count each word separately...

Dan

Originally Posted by ciapul12

... to count each word separately...

I'm not sure what you mean by this. Do you mean count the number of letters that make up each word?

Pete

Thank You both for formulas.
I probably haven't explained exactly what my aim is, my apologies for that.

I have attached a spreadsheet that shows exactly what I'm after.
Data tab contains product names and column with fail reasons separated by comma.
Another tab called List, contains list of all Fail reasons and that's where I would like to have a calculation so that I can compare both lists and count if mach is found (problem is where words are separated by comma and need to be treated as separate fail reasons)

Dan

I think you are looking for something like this in C2 of the List sheet:

=COUNTIFS(Data!B:B,"*"&B2&"*",Data!A:A,A2)

The asterisks around the B2 term are wildcard characters, which means any characters on either side (essentially "contains").

However, I see that you have "Rot" and "Rots" (and also "Hard Rot" and "Collapsing rot") as FailureReasons, and so the value for "Rot" would also include those other reasons as it is a substring, so it might not be completely accurate.

Hope this helps.

Pete

Hi Pete,
WOW this is amazing and it actually works!
I'd like to include more fields into my data so that I can use slicers on pivot table but the formula you provided me with doesn't work for this purpose..
Would you be able to tell me how this can be achieved?

I need to be able to filter data once summarized by Fail type.

Thank you kindly for your help.

Dan

Glad to hear that it worked for you.

Slicers were not available in XL2007 (which I use), so I can't help you with them. Hopefully someone else will chip in.

Pete