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

Onione

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
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 two12375
threeone
12375two

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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
If your text is in cell B2 Then in C2 put:
Excel Formula:
=LEN(B2)-LEN(SUBSTITUTE(B2," ","") )+1
 

Onione

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
If your text is in cell B2 Then in C2 put:
Excel Formula:
=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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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
ABC
1
2
3ab bc cd de ef ab fg bc gh ab
4
5
67
Sheet1
Cell Formulas
RangeFormula
C6C6=countUnique("A3"," ")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
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
ABC
1
2
3ab bc cd de ef ab fg bc gh ab
4
5
67
Sheet1
Cell Formulas
RangeFormula
C6C6=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.
VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
Give this function a try...
VBA Code:
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

New Member
Joined
Apr 4, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
Give this function a try...
VBA Code:
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,873
Messages
5,627,398
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top