How to remove duplication in text string?

  • Thread starter Thread starter geniusideas
  • Start date Start date
G

geniusideas

Hi,

I have text string with Comma separated for example:
AAAA,CCCC,BBBBBB,CCCC,DDDD,CCCC,DDDD,BBBBBB
what I need to do is to remove duplication as below:
AAAA,BBBBBB,CCCC,DDDD
Any one can help with VBA code..
Thanks.
 
I have text string with Comma separated for example:
AAAA,CCCC,BBBBBB,CCCC,DDDD,CCCC,DDDD,BBBBBB
what I need to do is to remove duplication as below:
AAAA,BBBBBB,CCCC,DDDD
Any one can help with VBA code..
Click to expand...

Give this function (which can be used as a UDF) a try...

Function DeDupe(ByVal S As String, Optional Delimiter As String = ",") As
String
Dim X As Long, Parts() As String, TwoParts() As String
Parts = Split(S, Delimiter)
For X = 0 To UBound(Parts)
TwoParts = Split(S, Parts(X), 2)
TwoParts(1) = Replace(TwoParts(1), Parts(X), "")
S = Join(TwoParts, Parts(X))
Next
Do While InStr(S, Delimiter & Delimiter)
S = Replace(S, Delimiter & Delimiter, Delimiter)
Loop
If Right(S, 1) = "," Then S = Left(S, Len(S) - 1)
DeDupe = S
End Function

Rick Rothstein (MVP - Excel)
 
Back
Top