Remove duplicates within Excel cell
Solution 1
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
Put the code above in a module
Use =RemoveDupes2(A2,",")
A2 contains repeated text separated by ,
You may change the delimiter
Solution 2
Assuming you'll never have more than two distinct names in a 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 these:
John John John Mary Mary
John Mary
John Mary John Mary
John Mary Mary
John John Mary
It will show John
for all of these:
John
John John
John John John
And it will show nothing if A1
is blank.
Comments
-
Waldir Leoncio almost 4 years
Say I have the following text string in one single Excel cell:
John John John Mary Mary
I want to create a formula (so no menu functions or VBA, please) that would give me, on another cell
John Mary
How can I do this?
What I've tried so far was search the internet and SO about the issue and all I could find were solutions involving Excel's built-in duplicate removal or something involving
countif
and the replacement of duplicates for""
. I've also taken a look at the list of Excel functions, especially those from the "Text" category, but couldn't find anything interesting, that could be done on one cell.