从包含逗号分隔文本的单元格中提取唯一字符 [英] Extract unique characters from cells containing comma separated text

问题描述

我有一个单元格列表，其值如下:

I have a list of cells with values like the below:

a,a,b,c,d
f,g,h,h,h,j
a,b,b
f,f,f,y,y,u,u

我想要一个可以给我以下内容的公式(上面的唯一列表).我应该能够将其写成一行并复制下来.

I want a formula that will give me the below (unique list of above). I should be able to write it for one row and copy it down.

a,b,c,d
f,g,h,j
a,b
f,y,u

推荐答案

无法使用仅使用内置工作表函数将逗号分隔的唯一结果返回到一个单元格的公式来执行此操作.

There is no way to do this with a formula that will return comma-separated unique results into one cell, using only the built-in worksheet functions.

但是，使用用户定义函数(UDF)来实现相同的功能非常简单.

But, it is very simple to achieve the same thing with a User Defined Function (UDF).

只需将此小例程放在标准代码模块中即可:

Just place this small routine in a standard code module:

Public Function UniqueList(s)
    Dim i&, k$, v
    v = Split(s, ",")
    For i = 0 To UBound(v)
        If InStr(k, v(i)) = 0 Then k = k & "," & v(i)
    Next
    UniqueList = Mid$(k, 2)
End Function

如果您的源字符串在单元格A1中，则在单元格B1中输入以下公式:

If your source string is in cell A1 then in cell B1 enter this formula:

=UniqueList(A1)

就是这样.现在，根据需要向下复制公式.

That's it. Now copy the formula downward as far as needed.

这篇关于从包含逗号分隔文本的单元格中提取唯一字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！