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

问题描述

说我在一个单独的Excel单元格中有以下文本字符串：

  John John John Mary Mary 
  

我想创建一个公式（所以没有菜单功能或VBA，请给我），另一个单元格

  John Mary 
  

我如何做到这一点？

我迄今为止所尝试的是搜索互联网，并了解有关问题的一切，我可以找到的是涉及Excel的内置解决方案 - 重复删除或涉及 countif 的东西，并替换的重复项。我也看了Excel函数的列表，特别是那些来自文本类别的列表，但是找不到有趣的东西，可以在一个单元格上完成。

解决方案

答案在这里： https://www.extendoffice.com/documents/excel/2133-excel-remove-duplicate-characters-in-string.html

 函数RemoveDupes2（txt As String，可选delim As String =）As String 
 Dim x 
'Updateby20140924 
使用CreateObject（Scripting.Dictionary）
 .CompareMode = vbTextCompare 
对于每个x在拆分（txt，delim）
如果Trim（x）<> 而不是.exists（Trim（x））Then .Add Trim（x），Nothing 
 Next 
如果.Count> 0 Then RemoveDupes2 = Join（.keys，delim）
 End with 
 End Function 
  

将代码放在模块中

使用 = remove（A2，，）
A2包含由
分隔的重复文本您可以更改分隔符

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.

解决方案

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 =remove(A2,",") A2 contains repeated text separated by , You may change the delimiter

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