vba 如何删除以 / 分隔的单元格中的重复值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15805157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I remove duplicate values in a cell seperated with a /?
提问by user1771351
I have multiple cells in excel that have as follows:
我在excel中有多个单元格，如下所示：
b1= E4I8/E4I8/E4I8/E4I8
b2=D3B2/B30C1/D3B2/D3B2/D3B2/B30C1
multiple /xxxx/
How do I remove these duplicate text strings in the same cell?
如何删除同一单元格中的这些重复文本字符串？
Thank you
谢谢
回答by Zev Spitz
This function uses the keys of a dictionary to build a unique list of parts from a passed-in string (add a reference to Microsoft Scripting Runtime):
此函数使用字典的键从传入的字符串构建唯一的部分列表（添加对 的引用Microsoft Scripting Runtime）：
Public Function UniqueParts(separator As String, toParse As String) As String
Dim d As New Scripting.Dictionary, part As Variant, i As Integer
For Each part In Split(toParse, separator)
d(part) = 1
Next
UniqueParts = Join(d.Keys, "/")
End Function
You can use this function in an Excel formula:
您可以在 Excel 公式中使用此函数：
=UniqueParts("/","E4I8/E4I8/E4I8/E4I8")
or with a cell reference:
或使用单元格引用：
=UniqueParts("/",B2)
You can also use this inside a macro that iterates over a range of cells.
您还可以在迭代一系列单元格的宏中使用它。
回答by Kazimierz Jawor
If using VBA is allowed to solve you problem I propose the following function: (no references required!)
如果允许使用 VBA 来解决您的问题，我建议使用以下功能：（不需要参考！）
Function UniqueFromCell(rngCell, splitString)
Dim myCol As New Collection
Dim itmCol
Dim i As Long
Dim arrTMP As Variant
arrTMP = Split(rngCell, splitString)
For i = 1 To UBound(arrTMP)
On Error Resume Next
myCol.Add arrTMP(i), CStr(arrTMP(i))
On Error GoTo 0
Next i
Dim result
For Each itmCol In myCol
result = result & itmCol & splitString
Next
UniqueFromCell = Left(result, Len(result) - Len(splitString))
End Function
To call it in Excel you need to put two parameters: cell and separation mark:
要在 Excel 中调用它，您需要输入两个参数：单元格和分隔标记：
=UniqueFromCell(B4,"/")
