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