Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove duplicates within Excel cell

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.

like image 266
Waldir Leoncio Avatar asked Sep 17 '14 18:09

Waldir Leoncio


People also ask

What formula can I use to remove duplicates in Excel?

The function UNIQUE returns the unique values contained in a column. It is only available only for Excel Microsoft 365 or Excel Online.

How do you remove duplicates but leave the highest value in another column in Excel?

If you want to remove all duplicates but leave the highest ones, you can apply this formula =MAX(IF($A$2:$A$12=D2,$B$2:$B$12)), remember to press Shift + Ctrl + Enter keys.


2 Answers

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

like image 111
Madhav Kulkarni Avatar answered Oct 01 '22 06:10

Madhav Kulkarni


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.

like image 20
Rick Hitchcock Avatar answered Oct 01 '22 05:10

Rick Hitchcock