String manipulation

[Martin Dashper]

I have a delimited string eg. dog;cat;horse;cat;
How can I process this in order to remove the duplicate items?

Martin Dashper

 

[mscertified]

You could create a table with a unique key, parse and insert each item to the
table. Then read back the table and reform the string. You could also use an
array or collection instead of a table but the table conveniently throws away
duplicates for you.

Dorian

 

[John Nurick]

Something like this, perhaps:

Function RemoveDuplicateItems(V As Variant, Delimiter As String) _
As Variant

'Removes duplicate items from a string containing a list of items
'separated by Delimiter.

Dim Uniques As Object 'Scripting.Dictionary
Dim Item As Variant

If IsNull(V) Then
RemoveDuplicateItems = Null
Exit Function
End If

Set Uniques = CreateObject("Scripting.Dictionary")

For Each Item In Split(CStr(V), Delimiter)
If Not Uniques.Exists(Item) Then
Uniques.Add Item, 0
End If
Next

RemoveDuplicateItems = Join(Uniques.Keys, Delimiter)
End Function

 

[Martin Dashper]

Many thanks. I was going to go down the 'nested loops and incrementing
or not incrementing flags' route. This is so much neater.

Martin