Remove duplicate within cell.

this vba may work

Sub Remove_DupesInString()
     ' this puts the final value in column J
    Dim starval As String
    Dim finval As String
    Dim strarray() As String
    Dim x As Long
    Dim k As Long
     ' step through each cell in range
    For Each cell In Sheets(1).Range("A1:A500")
        Erase strarray ' erase array
        finval = "" ' erase final value"
        starval = cell.Value
         
        strarray = Split(starval, ",")
         
         'Step through length of string and look for duplicate
        For rw = 0 To UBound(strarray)
             
            For k = rw + 1 To UBound(strarray)
                If Trim(strarray(k)) = Trim(strarray(rw)) Then
                    strarray(k) = "" 'if duplicate clear array value
                End If
            Next k
        Next rw
         
         ' combine all value in string less duplicate
        For x = 0 To UBound(strarray)
            If strarray(x) <> "" Then
                 
                finval = finval & Trim(strarray(x)) & ", " 
            End If
             
        Next x
         ' remove last space and comma
        finval = Trim(finval)
        finval = Left(finval, Len(finval) - 1)
         ' output value to Column J
        cell.Offset(0, 9).Value = finval
         
         
    Next cell
     
End Sub

This should be faster and easier to use on a single cell...

paste the code into a new module in the workbook and then use =RemoveInCellDuplicates as a normal function.

Option Explicit

Function RemoveInCellDuplicates(inString As String) As String
Dim arr, x As Long
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")

arr = Split(inString, ",")

For x = 0 To UBound(arr)
    If Not Dic.exists(arr(x)) Then Dic.Add arr(x), arr(x)
Next x

RemoveInCellDuplicates = Join(Dic.Items, ",")

End Function

Select allOpen in new window

RemoveInCellDuplicates.xlsm

Thank you Emes.

I'm getting a run time error '5'.

When I go to debug, it is highlighting the following line:  finval = Left(finval, Len(finval) - 1)

Solution?

FF

Thank you The_Barman.

I was about to accept your solution when I found a hiccup... it would seem that when the first number is repeated the function does not remove the duplicate.

For example:

328, 328, 482, 482  is coming back 328, 328, 482

Any ideas?

FF

Excellent. Many Thanks!