VBA Access Excel Remove duplicates in text

This tutorial provides a custom Access Excel VBA Function to remove duplicates in text, parameters provide flexibility of case sensitivity and delimiter

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

Press Alt+F11 > Insert Module > Paste the below code

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

 

Formula	Return Value
=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)

Outbound References

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/delete-duplicate-value-in-a-row/a79acf8e-8dfd-4c4b-b8c0-e250dabe98dc

 

VBA Access Excel Remove duplicates in text