CODEWITHSUNDEEP
excelfunctionexcel-formulavba
Devil07
Devil07

Reputation: 141

Is there a function in Excel that removes duplicate strings from within a cell?

The UDF and other solutions offered in other questions do not solve this problem. Perhaps it is the length of the strings, and then number of duplicates in my problem. (All numbers, separated by commas, 13 digits long, 2 to 3 unique numbers repeated approximately 20-40 times in a single cell.)

I reviewed the answers to this question but those answers didn't work properly with my data.

Is there a function or a formula that I can use to remove duplicate strings within a single cell?

If,

A1=10,10,10,10,11,11,12,12,12 (the actual numbers are 13 digits each and they are either 2 or 3 unique numbers that repeat. I just need to capture each unique number)

Is there a simple way like RemoveDups() that will remove the duplicates and leave just . A1=10,11,12? Or do I need to create a UDF?

Upvotes: 0

Views: 538

Answers (1)

Sixthsense
Sixthsense

Reputation: 1971

Function RemoveDuplicates(v As Variant) As String
Dim aSplit As Variant, aUnique() As Variant, vMatch As Variant, a As Variant

aSplit = Split(v, ",")

ReDim Preserve aUnique(0 To x)
aUnique(0) = Application.WorksheetFunction.Rept("|^|", 20)

For Each a In aSplit
    vMatch = Application.Match(Trim(a), aUnique, 0)

    If IsError(vMatch) Then
        x = x + 1
        ReDim Preserve aUnique(0 To x)
        aUnique(x) = Trim(a)
    End If
Next a

RemoveDuplicates = Join(Filter(aUnique, aUnique(0), False), ",")

End Function

Upvotes: 3

Related Questions