Count non-repeated words in cell

Hi,

I want to count the nr of words in cell without counting the repeated words.
For example, in "this is a ball and that is a square", the total nr of words is 9, and the total nr of non-repeated words is 7, because "is" and "a" are repeated twice, and I just want to count it once.

Please help!

Thanks,
mxc668

Assuming sentence is in A1, try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

This formula allows for up to 100 words in the cell...

Only replace the red A1's to reference another cell...

If you have more than 100 words adjust the occurrences $A$100 to suit.

Sorry. I know how to use the fomula now. Thanks a lot! But maybe I did not make it clear that sometimes a word can be repeated more than once. Using real data, in cell A1 I have "um a pink circle with a line across it rolled across the grey rectangle and um nudge the blue square to the end of the grey rectangle", by using the fomula, I got 21. Actually, the number should be 19, because "the" has appeared four times in stead of twice.

Could you please tell me how to deal with it?

Thanks,
mxc668

Comfortable with a VBA solution?

FWIW I'm with shg - this has UDF written all over it given sheer complexity required in native approach.

I suspect shg has some tricks up his sleeve but here's one possible approach using the Dictionary Object

Please Login or Register  to view this content.

The above would be stored in a Module in VBE (in Macro Enabled file) and called from a cell along lines of

=UNIQUEWORDS(A1)

A default delimiter of space is assumed, if that's not the case then pass optional parameter of delimiter, eg:

=UNIQUEWORDS(A1,":")

would count unique words split by colon

From PM:

Hi NBVC,

You kindly gave me the formula "=COUNT(1/FREQUENCY(IF(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",100)),ROW($A$1:$A$100)*100,100))<>"",MATCH(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",100)),ROW($A$1:$A$100)*100,100)),TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",100)),ROW($A$1:$A$100)*100,100)),0)),ROW($A$1:$A$100)))" to count the nr of non-repeated words in a cell.

Thanks a lot! But I did not make it clear that sometimes a word can be repeated more than once. Using real data, in cell A1 I have "um a pink circle with a line across it rolled across the grey rectangle and um nudge the blue square to the end of the grey rectangle", by using the fomula, I got 21. Actually, the number should be 19, because "the" has appeared four times in stead of twice.
Btw, the maximum of of words in a cell for my data is 500.

Could you please tell me how to deal with it? I really need it to be done for my project.

Thanks,
mxc668

OP has added that he can may have up to 500 words in the cell...

in that case, this formula should do it...

Please Login or Register  to view this content.

Hi again mxc668,

thanks to daddylonglegs who has kindly alerted me that my last formula may give you potentially the same issue as before as you approach those 500 words...

I mistakingly tested the formula for 500 characters and it seemed ok... but 500 words will undoubtedly take you to well over 1000 or 2000 chars and I am not certain the formula will always return the correct result... you may come across the phenomenon pointed out by daddylonglegs earlier.

... so, in short, I think you better look into DonkeyOte's (or shg's...if he has an alternative) UDF approaches.