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

#### Onione

##### New Member
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!

### 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
If your text is in cell B2 Then in C2 put:
Excel Formula:
``=LEN(B2)-LEN(SUBSTITUTE(B2," ","") )+1``

#### Onione

##### New Member
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
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

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

Replies
2
Views
95
Replies
12
Views
173
Replies
1
Views
91
Replies
1
Views
179
Replies
8
Views
256

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.

### Which adblocker are you using?

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

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