Formula to count how many individual words are in a cell in a range

[Onione]

Is it possible to have a formula give a total word count in a given range excluding duplicates? I can only figure out how to count spaces. please see the example below A1:B3

one two	12375
three	one
12375	two

Based on that example I would like the formula to display 4. for the life of me I cant seem to get this right. I would like to stay away from vba to keep compatibility between pc and mobile. the final sheet will have 20 rows if that matters. I really hope this is possible.

Thanks for taking the time to read!

 

[JLGWhiz]

If your text is in cell B2 Then in C2 put:

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

 

[Onione]

If your text is in cell B2 Then in C2 put:

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

Thanks but I have already done something like this. All this is doing is counting spaces. I'm trying to find a formula that will count individual instances ignoring duplicates. I'm starting to think this is impossible and I will need to make helper cells.

 

[JLGWhiz]

Function countUnique(cel As String, delim As Variant) As Long
Dim spl As Variant, cnt As Long, dep As String
spl = Split(Range(cel).Value, delim)
    dep = spl(0)
    cnt = 1
    For i = 1 To UBound(spl)
        If InStr(dep, spl(i)) = 0 Then
            dep = dep & "," & spl(i)
            cnt = cnt + 1
        End If
    Next
countUnique = cnt
End Function

copy this fundtion to your standard code module. Then use it as shown below.

Discussions.xlsm
	A	B	C
1
2
3	ab bc cd de ef ab fg bc gh ab
4
5
6			7
Sheet1

Cell Formulas
Range	Formula
C6	C6	=countUnique("A3"," ")

 

[Rick Rothstein]

Here is another way to write the CountUnique function. Note that instead of passing the quoted cell address, you pass the actual (unquoted) cell address to my function (you can also pass a quoted text string as well). Also note that I have made the Delim argument optional and defaulted it to a space character if omitted.

Function CountUnique(CelVal As String, Optional Delim As String = " ") As Long
  Dim V As Variant
  With CreateObject("Scripting.Dictionary")
    For Each V In Split(Application.Trim(CelVal), Delim)
      .Item(V) = 1
    Next
    CountUnique = .Count
  End With
End Function

 

[Onione]

Function countUnique(cel As String, delim As Variant) As Long
Dim spl As Variant, cnt As Long, dep As String
spl = Split(Range(cel).Value, delim)
    dep = spl(0)
    cnt = 1
    For i = 1 To UBound(spl)
        If InStr(dep, spl(i)) = 0 Then
            dep = dep & "," & spl(i)
            cnt = cnt + 1
        End If
    Next
countUnique = cnt
End Function

copy this fundtion to your standard code module. Then use it as shown below.

Discussions.xlsm
	A	B	C
1
2
3	ab bc cd de ef ab fg bc gh ab
4
5
6			7
Sheet1

Cell Formulas
Range	Formula
C6	C6	=countUnique("A3"," ")

Here is another way to write the CountUnique function. Note that instead of passing the quoted cell address, you pass the actual (unquoted) cell address to my function (you can also pass a quoted text string as well). Also note that I have made the Delim argument optional and defaulted it to a space character if omitted.

Function CountUnique(CelVal As String, Optional Delim As String = " ") As Long
  Dim V As Variant
  With CreateObject("Scripting.Dictionary")
    For Each V In Split(Application.Trim(CelVal), Delim)
      .Item(V) = 1
    Next
    CountUnique = .Count
  End With
End Function

Okay, this is great guys. not working on mobile but thats not such a big deal. I like how Ricks will display changes automatically. my one issue is I cant seem to add multiple cells. for example =countUnique(C2:D20, " ") gives #VALUE!. Any idea on how to make that work?

 

[Rick Rothstein]

Give this function a try...

Function CountUnique(Rng As Range, Optional Delim As String = " ") As Long
  Dim V As Variant, Cell As Range
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Rng
      For Each V In Split(Application.Trim(Cell.Value), Delim)
        .Item(V) = 1
      Next
    Next
    CountUnique = .Count
  End With
End Function

 

[Onione]

Give this function a try...

Function CountUnique(Rng As Range, Optional Delim As String = " ") As Long
  Dim V As Variant, Cell As Range
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Rng
      For Each V In Split(Application.Trim(Cell.Value), Delim)
        .Item(V) = 1
      Next
    Next
    CountUnique = .Count
  End With
End Function

Perfect! Thanks!!