# 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

The answer is here: https://www.extendoffice.com/documents/excel/2133-excel-remove-duplicate-characters-in-string.html

```
Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
Dim x
'Updateby20140924
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
Next
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

source to share

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
E2: =TRIM(CONCATENATE(D2," ",E1))
```

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:

```
=LOOKUP(REPT("z",99),$E$1:$E$100)
```

source to share

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 John
```

And it will show nothing if `A1`

empty.

source to share