This tutorial provides a custom Access Excel VBA Function to remove duplicates in text, parameters provide flexibility of case sensitivity and delimiter
You may also want to read:
VBA Access Excel remove duplicates in text
This tutorial is to help you to remove duplicates in text (in a Cell), but it is not about removing duplicated Cell value in a column. To remove duplicated Cell value in a column, highlight the column, navigate to menu Data > Remove Duplicates.
Below is an example of duplicate in text, lets say Cell A1 contains the followings
|Mary, Ann, MAry, Peter, Mary,PETER, Mary|
You can see that Mary has been duplicated for 3 times differently, so is Peter. Our goal is to return only one Mary and Peter, as well as other non duplicated value.
VBA Function Code – remove duplicates in text
The below code makes use of the unique properties of associative array Dictionary that Key cannot be duplicate.
The purpose is to create an associate array called objDict to add delimited items as Key. If Item already exists, do not return the item in Function.
Public Function wUniqueStr(sinput As String, delimiter As String, Optional Compare As Integer = 0) As String Dim objDict As Object Dim arrInput As Variant Dim uniqStr As String arrInput = Split(sinput, delimiter) Set objDict = CreateObject("Scripting.Dictionary") If Compare = 0 Then 'case insensitive For i = 0 To UBound(arrInput) If objDict.exists(UCase(Trim(arrInput(i)))) Then Else objDict.Add UCase(Trim(arrInput(i))), i uniqStr = uniqStr & arrInput(i) & delimiter End If Next i wUniqueStr = Left(uniqStr, Len(uniqStr) - Len(delimiter)) Else 'case sensitive For i = 0 To UBound(arrInput) If objDict.exists(Trim(arrInput(i))) Then Else objDict.Add Trim(arrInput(i)), i uniqStr = uniqStr & arrInput(i) & delimiter End If Next i wUniqueStr = Left(uniqStr, Len(uniqStr) - Len(delimiter)) End If End Function
Syntax of Access Excel VBA Function – remove duplicates in text
wUniqueStr(sinput, delimiter, [Compare])
|sinput||The text you want to remove duplicate|
|delimiter||The seperator (delimiter) that separates each value|
|Compare||Optional. Default is 0, case insensitive. Type 1 for case sensitive|
Example of Access Excel VBA Function – remove duplicates in text
Assume you have the followings in Cell A1
|Mary , Ann, MAry, Peter, Mary,PETER, Mary|
|=wUniqueStr(A1,”,”,1)||Mary , Ann, MAry, Peter,PETER|
|=wUniqueStr(A1,”,”)||Mary , Ann, Peter|
Note the followings
– Any “Mary ” and “Mary ” (with space in front or behind) is regarded as the same text, only the left most version (first occurrence) of “Mary” is returned
– “Peter” and “PETER” are regarded as same value if the third parameter is “1” (case sensitive)