excel表格同一单元格里剔除重复词怎么做？

1. 首先调出"开发工具"这个菜单. 在选项里面.

<noscript> image

</noscript>

image

2. 点开VBA, 进入VBA界面

<noscript> image

</noscript>

image

3. 插入一个模块, 处女座可以对这个模块起个好名字. 老衲是水瓶座, 那就哈哈哈哈啦.

<noscript> image

</noscript>

image

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. 这时候你已经有一个新公式了

<noscript> image

</noscript>

image

6. 这个函数只需要把左边的列选中, 然后拖动一下, 问题解决. 完美~~

<noscript> image

</noscript>

image

如果还有更多问题, 请关注老衲在知乎的专栏~~从点滴开始做更好的自己 - 知乎专栏

编辑于 2016-10-19

​赞同 35 ​ ​16 条评论

​分享

​收藏 ​喜欢

​

收起​

<meta itemprop="name" content="黄晨"><meta itemprop="image" content="https://pic4.zhimg.com/v2-20a25efce4b05a573a67c7e9d3be4635_is.jpg"><meta itemprop="url" content="https://www.zhihu.com/people/huangchen1031"><meta itemprop="zhihu:followerCount" content="2265">

黄晨

黄晨

不答题/不回信/不会Excel

1 人赞同了该回答

<meta itemprop="image"><meta itemprop="upvoteCount" content="1"><meta itemprop="url" content="https://www.zhihu.com/question/51729819/answer/127369448"><meta itemprop="dateCreated" content="2016-10-19T09:53:04.000Z"><meta itemprop="dateModified" content="2016-10-20T02:10:19.000Z"><meta itemprop="commentCount" content="3">

简单写了下，利用字典去重

Function duplicate_removal(ByVal str As String, ByVal sign As String)

Dim strArr, strDic
Set strDic = CreateObject("Scripting.Dictionary")

strArr = Split(str, sign)
For i = 1 To UBound(strArr)
  strDic.Item(strArr(i)) = strArr(i)
Next i

For Each Key In strDic
  duplicate_removal = duplicate_removal + strDic(Key) + sign
Next

duplicate_removal = Left(duplicate_removal, Len(duplicate_removal) - Len(sign))

End Function

使用例子：
对A1单元格去重，分隔符是中文逗号
=duplicate_removal(A1,"，")