更新时间:2023-02-13 18:27:13
答案在这里: 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