count unique semin comma separated words in cell

[peter_sjogarde]

I want to count unique occurrences of a word in a cell. The words are separated with semi comma and the number of words in each cell varies. The cell could be empty.

Example

Column A
Sweden;USA;Sweden
USA,Norway,Canada
USA,USA
[emptycell]

I would like the answer in column B, which should be:
2
3
1
0

I am thankful for any help!

 

[pgc01]

Hi Peter

Try this udf to count duplicate words in a semicolon separated list of words.

Assumption: a word is a sequence of non-semicolon characters delimited by a semicolon or the start or end of line

Function CountUniq(s As String) As Long
Dim coll As Collection
Dim v As Variant, vE As Variant
 
Set coll = New Collection
v = Split(s, ";")
On Error Resume Next
For Each vE In v
    coll.Add 1, vE
Next vE
CountUniq = coll.Count
End Function

 

[peter_sjogarde]

Terrific!

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[captainentropy]

Hi Peter

Try this udf to count duplicate words in a semicolon separated list of words.

Assumption: a word is a sequence of non-semicolon characters delimited by a semicolon or the start or end of line

Function CountUniq(s As String) As Long
Dim coll As Collection
Dim v As Variant, vE As Variant
 
Set coll = New Collection
v = Split(s, ";")
On Error Resume Next
For Each vE In v
    coll.Add 1, vE
Next vE
CountUniq = coll.Count
End Function

Nice function pgc1. What if in the OP's example you wanted to count all the uniques in A1:A4? The answer would be 4: Canada, Norway, Sweden, USA. Can the UDF be modified to do that?