CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
Karl Bishop
Karl Bishop

Reputation: 391

Google Sheets/Excel formula to reduce comma-separated lists to a particular subset

In a database extract, each row has a cell containing a comma-separated list of tags. I need a formula to filter each of these cells down to a particular subset.

I've got a rudimentary working version but it's horrifically verbose.

The tag list is in column C. The subset of tags I want to keep is currently in A1:A10 on a separate sheet (called Subset).

My rudimentary version concatenates the same sub-formula once for each cell in A1:A10. Each sub-formula tests if FIND(SubsetTag, TagList) returns a number and, if so, outputs the SubsetTag followed by ", " to build up a string containing all the matches e.g.

=IF(ISNUMBER(FIND(Subset!A$1,C2)),Subset!A$1&", ","") & IF(ISNUMBER(FIND(Subset!A$2,C2)),Subset!A$2&", ","") & IF(ISNUMBER(FIND(Subset!A$3,C2)),Subset!A$3&", ","") & IF(ISNUMBER(FIND(Subset!A$4,C2)),Subset!A$4&", ","") & IF(ISNUMBER(FIND(Subset!A$5,C2)),Subset!A$5&", ","") & IF(ISNUMBER(FIND(Subset!A$6,C2)),Subset!A$6&", ","") & IF(ISNUMBER(FIND(Subset!A$7,C2)),Subset!A$7&", ","") & IF(ISNUMBER(FIND(Subset!A$8,C2)),Subset!A$8&", ","") & IF(ISNUMBER(FIND(Subset!A$9,C2)),Subset!A$9&", ","") & IF(ISNUMBER(FIND(Subset!A$10,C2)),Subset!A$10&", ","")

Is there a good way to do this using formulas? (I'd like to avoid scripts)

Ideally I'd like to generalise the formula so it doesn't have to be updated when the subset grows beyond A1:A10 and would like to avoid the trailing comma :)

Upvotes: 2

Views: 229

Answers (2)

Tom Malkin
Tom Malkin

Reputation: 2284

You can do this with an array formula combined with CONCAT()

=LEFT(CONCAT(IF(ISNUMBER(FIND(M8:M10,J8)),M8:N10,"")),LEN(CONCAT(IF(ISNUMBER(FIND(M8:M10,J8)),M8:N10,"")))-2)

where:

  • M8:M10 is the list of tags,
  • N8:N10 is a list of the string ", "
  • J8 is the string you are searching

Make sure you press Control + Shift + Enter when you finish the formula.

The LEFT() part is to remove the final comma.

Upvotes: 2

user6655984
user6655984

Reputation:

Here is a Google Sheets solution, using regexmatch:

=join(",", filter(Subset!A1:A10, regexmatch(C2, "\b" & Subset!A1:A10 & "\b")))

The set in range A1:A10 is filtered down to those tags for which the string C2 matches a regular expression of the form \btag\b, meaning C2 must contain the tag as a separate word (\b is word boundary). The filtered array is then joined into a new comma-separated string.

If none of the tags are contained in C2, the formula returns error message #N/A. If you prefer empty output in this case, wrap the formula in =iferror().

Upvotes: 1

Related Questions