Count non-repeated words in cell

**mxc668** · 10-11-2009, 05:18 PM

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

**NBVC** · 10-11-2009, 08:47 PM

Assuming sentence is in A1, try:

=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)))

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.

**mxc668** · 10-12-2009, 03:19 PM

Thanks, but if you try "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" in cell A1, then paste the fomula in cell b1, you get 0 in B1. Please help me a bit more on this.

Best,
MXC668

**mxc668** · 10-12-2009, 03:20 PM

By the way, I am using excell 2003.

**mxc668** · 10-12-2009, 03:28 PM

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

**daddylonglegs** · 10-12-2009, 08:44 PM

In fact NBVC's formula is designed to only count the same word once, however many times it appears.

In this case the failure is because using 100 as the multiplier and the number of spaces means that they will progressively get out of sync due to the word lengths....in this case that meant that the second "rectangle" wasn't extracted in its entirety but as "r" and then "ectangle", thus counting two words that don't really exist, hence a count of 21 rather than 19.

Increasing the 100 multiplier by the average word length, e.g. to 104 for instance, would rectify the problem in most cases, i.e.

=COUNT(1/FREQUENCY(IF(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",100)),ROW($A$1:$A$100)*104,100))<>"",MATCH(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",100)),ROW($A$1:$A$100)*104,100)),TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",100)),ROW($A$1:$A$100)*104,100)),0)),ROW($A$1:$A$100)))

I'd be inclined to try for a more robust solution....but I haven't found it yet.....

**shg** · 10-12-2009, 11:33 PM

Comfortable with a VBA solution?

**DonkeyOte** · 10-13-2009, 03:48 AM

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

Function UniqueWords(rngStr As Range, Optional strDelim As String) As Long
Dim oDic As Object, lngUnique As Long, lngWord As Long, vWords As Variant
If strDelim = "" Then strDelim = " "
vWords = Split(rngStr.Value, strDelim)
Set oDic = CreateObject("Scripting.Dictionary")
For lngWord = LBound(vWords) To UBound(vWords) Step 1
    With oDic
        If Not .exists(vWords(lngWord)) Then
            lngUnique = lngUnique + 1
            .Add vWords(lngWord), lngUnique
        End If
    End With
Next lngWord
Set oDic = Nothing
UniqueWords = lngUnique
End Function

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

**NBVC** · 10-13-2009, 07:48 AM

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...

=COUNT(1/FREQUENCY(IF(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",500)),ROW($A$1:$A$500)*500,500))<>"",MATCH(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",500)),ROW($A$1:$A$500)*500,500)),TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",500)),ROW($A$1:$A$500)*500,500)),0)),ROW($A$1:$A$500)))