removing duplicates in a string of text

Hi Everyone,

I need your help! I have a spreadsheet that contains 1-3 email addresses in a single cell separated by a semi-colin. Some of the email addresses are duplicated. Is there a formula/function that can look at the email addresses in the cell, and return the email addresses removing the duplicates?

I have attached a sample document with my desired outcome.

Thanks in advance for the help,
Julie

Originally Posted by zapszipszops

Hi Everyone,

I need your help! I have a spreadsheet that contains 1-3 email addresses in a single cell separated by a semi-colin. Some of the email addresses are duplicated. Is there a formula/function that can look at the email addresses in the cell, and return the email addresses removing the duplicates?

I have attached a sample document with my desired outcome.

Thanks in advance for the help,
Julie

One solution would be to do it in two stages.

First convert the single cell to 3 cells with the Text to Columns functionality, specifying the ; as the delimiter.

Then in a fourth column, rebuild the cell with some nested If() functions which test the values of the 3 cells for duplicates and re-building the string as appropriate. You'll definitely need the first cell in the rebuilt string, so it's just a case of checking whether cells 2=1, or 3=1, or 2=3 and building the formula accordingly.

HTH

Hope this helps

Hi

Say your data as shown is in the range A1:A3.

Create a general module in your spreadsheet (alt F11, insert, module) and input the code

Function myfunc(xx)
  Dim nodupes As New Collection
  arr = Split(xx, ";")
  For i = LBound(arr) To UBound(arr)
    On Error Resume Next
    nodupes.Add arr(i), arr(i)
    On Error GoTo 0
  Next i
  
  For i = 1 To nodupes.Count
    holder = holder & nodupes(i) & ";"
  Next i
  
  myfunc = Left(holder, Len(holder) - 1)
  
  
End Function

In B1 enter the formula
=myfunc(A1)
and copy down to B3.

HTH

rylo