Remove duplicates in an Excel cell

Let's say I have the following text string in one Excel cell:

John John John Mary Mary


I want to create a formula (so no menu or VBA functions please) that would give me, in another cell

John Mary


How can i do this?

What I have tried so far is searching the internet and SO about this issue and all I could find are solutions related to Excel duplicate removal, or involving countif

and replacing duplicates for ""

. I also looked at the list of Excel functions, especially those in the "Text" category, but couldn't find anything interesting that could be done in a single cell.


source to share

4 answers

The answer is here:

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
Dim x
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each x In Split(txt, delim)
        If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
    If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
End With
End Function


Place the code above in a module

Use =remove(A2,",")

A2 contains duplicate text, separated, you can change the separator



As I already wrote, it is trivial to solve with VBA. If you can't use VBA, one method is to use helper columns.

Suppose your string is in A1 format

Enter the following formulas:

C1:  =IFERROR(INDEX(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW(INDIRECT("1:" & LEN($A$1)-LEN(SUBSTITUTE($A$1," ",""))+1))-1)*99+((ROW(INDIRECT("1:" & LEN($A$1)-LEN(SUBSTITUTE($A$1," ",""))+1))=1)),99)),ROWS($1:1),1),"")

D1:  =IF(COUNTIF(C1:$C$5,C1)=1,C1,"")


Select C1 and D1 and fill in until you start getting blanks.

E1:  =D1


Select E2 and fill in.

The content of the last cell filled with column E will be your result.

If you want to have a cell that automatically returns the content of the last cell in the E column range, you can use a formula like:





No formula: Text to Columns with space separator, transpose the output, apply Remove Duplicates to each of the columns separately, and then transpose the result.



Assuming there are no more than two different names in the cell, this should work:

=MID(A1&" ",1,FIND(" ",A1&" "))&
 MID(SUBSTITUTE(A1&" ",MID(A1&" ",1,FIND(" ",A1&" ")),"")&" ",1,
 FIND(" ",SUBSTITUTE(A1&" ",MID(A1&" ",1,FIND(" ",A1&" "))&" ","")))


It will show John Mary

for all of them:

John John John Mary Mary
John Mary
John Mary John Mary
John Mary Mary
John John Mary


It will show John

for all of them:

John John
John John John


And it will show nothing if A1




All Articles