regex - Concatenating a range while removing duplicate values as well as specific values

Question

I have a sheet that looks something like this:

What I'd like to be able to do is concatenate this range while removing duplicate values, but also excluding certain other values.

For example: I'd like to be able to see cat once (note it is in the sheet twice), but not see Mouse at all (also in the sheet twice).

It would also be great if I could do this while also adding a space in between each concatenated value.

标签： regexgoogle-sheetsfilteruniquetextjoin

Answer 1

利用：

=TEXTJOIN(" ", 1, UNIQUE(FILTER(A:A, NOT(REGEXMATCH(LOWER(A:A), "mouse")))))

如果您想排除更多值，请执行以下操作：

=TEXTJOIN(" ", 1, UNIQUE(FILTER(A:A, NOT(REGEXMATCH(LOWER(A:A), "mouse|tree")))))

如果您的数据集包含数字，请执行以下操作：

=TEXTJOIN(" ", 1, UNIQUE(FILTER(A:A, NOT(REGEXMATCH(TO_TEXT(LOWER(A:A)), 
 "mouse|tree|1238")))))