excel表格同一单元格里删除重复词
转自知乎问题:同一个单元格里删除重复词,只保留一个唯一词怎么做 ?
如下图红框,有重复词:2个“舒缓”,3个“伤感”,效果是要删掉的词有:1个“舒缓”,2个“伤感”,只留下都是唯一词。
解决方案:
作者:靳伟
链接:https://www.zhihu.com/question/51729819/answer/127363442
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
思考再三, 老衲承认, 没啥简单的办法. 只好祭出自定义函数了. (其他法子这个更麻烦)
首先, 如果你看过老衲的Excel教程, 应该还记得怎么创建一个自定义函数. 忘了? 没关系, 老衲再演示一遍.
1. 首先调出"开发工具"这个菜单. 在选项里面.
2. 点开VBA, 进入VBA界面
3. 插入一个模块, 处女座可以对这个模块起个好名字. 老衲是水瓶座, 那就哈哈哈哈啦.
4. Coding: 时间紧就不逐一细说了. 还是非常简单的. 老衲的注释率超过20%啦~
Public Function deduplicate(duplicateWords As String)
'declaim some vars
Dim wArray As Variant
'Split the long string and Write in and deduplicate with dictionary
wArray = Split(duplicateWords, ",")
Set dic = CreateObject("scripting.dictionary")
For i = 0 To UBound(wArray)
dic(Trim(wArray(i))) = "" 'Trim the string in case of irragular text
Next
'Reconstruct the long string
Dim result As String
For Each wItem In dic
result = result + "," + wItem
Next
deduplicate = Right(result, Len(result) - 1) 'remove the extra comma and return
End Function
5. 这时候你已经有一个新公式了
6. 这个函数只需要把左边的列选中, 然后拖动一下, 问题解决. ~~
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
作者:jaffedream
链接:https://www.zhihu.com/question/51729819/answer/129509001
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
在靳伟大师代码的基础上,把【词语的分隔符】设为参数变量,应该完美了。
Public Function deduplicate(duplicateWords As String, sign As String)
Dim result As String: Set dic = CreateObject("scripting.dictionary")
If Len(sign) >= 1 Then
Dim wArray As Variant: wArray = Split(duplicateWords, sign)
For i = 0 To UBound(wArray): dic(Trim(wArray(i))) = ""
Next
For Each wItem In dic: result = result + sign + wItem
Next
deduplicate = Right(result, Len(result) - 1)
ElseIf Len(sign) = 0 Then
For i = 0 To Len(duplicateWords) - 1: dic(Trim(Mid(duplicateWords, i + 1, 1))) = ""
Next
For Each wItem In dic: result = result + sign + wItem
Next
deduplicate = result
End If
End Function
调用函数时,传入【单元格和分隔符】,效果如下:
带分隔符效果
无分隔符效果
上一篇: 插入排序
推荐阅读