Question

Excel 重复文字的筛选？

如图，一个单元格内重复了相同的词，怎么能把相同的词都抠出来？B1是想要的结果。

Answer 1

=重复词(A1)

=去重词(A1)

Function 重复词(a As String)

Set d = CreateObject("scripting.dictionary")

Set reg = CreateObject("vbscript.regexp")

With reg

    .Global = True

    .Pattern = "(.{2,}).*\1"

    Do

        Set sj = .Execute(a)

        If sj.Count = 0 Then Exit Do

        fz = sj(0).submatches(0)

        d(fz) = ""

        a = Replace(a, fz, "")

    Loop

    重复词 = Replace(Join(d.keys, "、"), "，", "")

End With

End Function

Function 去重词(a As String)

Set d = CreateObject("scripting.dictionary")

b = Split(a, "，")

For i = 0 To UBound(b)

    d(b(i)) = ""

Next

去重词 = Join(d.keys, "、")

End Function

Answer 2

使用新版office按下面公式完成：

=TEXTJOIN("、",,UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(A1,"、","</b><b>")&"</b></a>","//b")))