删除 Excel 单元格中的重复项 [英] Remove duplicates within Excel cell

问题描述

假设我在一个 Excel 单元格中有以下文本字符串:

Say I have the following text string in one single Excel cell:

John John John Mary Mary

我想在另一个单元格上创建一个公式(所以请不要使用菜单功能或 VBA)

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

John Mary

我该怎么做?

到目前为止我已经尝试在互联网上搜索有关该问题的内容，我所能找到的只是涉及 Excel 的内置重复删除或涉及 countif 和替换重复项的解决方案<代码>"".我还查看了 Excel 函数列表，尤其是文本"类别中的函数列表，但找不到任何可以在一个单元格上完成的有趣内容.

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.

推荐答案

答案在这里: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

使用 =RemoveDupes2(A2,",")A2 包含由 , 分隔的重复文本您可以更改分隔符

Use =RemoveDupes2(A2,",") A2 contains repeated text separated by , You may change the delimiter

这篇关于删除 Excel 单元格中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！