Google-sheets – “-Ls 1000” in Google Sheets

Instead of filter views, I would use the filter command. For example, suppose Sheet1 holds raw data, with A being a character, B the list of "mentioned" chapters, and C the list of "appeared" chapters. You can then add another sheet for "filtered" data, where the user enters chapter number in, e.g., cell A1 and immediately get the list of rows matching that number thanks to the command

=filter(Sheet1!A2:C, regexmatch(Sheet1!B2:B,"\b"&A1&"\b") + regexmatch(Sheet1!C2:C,"\b"&A1&"\b"))

Here, each regexmatch scans its column (B or C) paying attention to word boundaries (\b) so that, e.g., "20,23,25" would not match "3". Addition plays the role of OR operator here.

If you are sharing this, you can allow editing but protect all cells except A1 on the second sheet: this way, the users can only change the chapter number for filtering.

There is a potential drawback: the changes to A1 are actual edits; i.e., what one user does affects everyone. If this is unacceptable, then filter views are the only option. You can circumvent the "OR" issue by creating a new column with

=B2&","C2

i.e., concatenation of columns B and C. Then apply filter views based on that column. However, the filter view will require either

• custom formula with regexmatch as above (not user-friendly at all)
• different format of data to make the condition "text contains..." work: for example, you could format as ,2,4,10,13,16, so that the text to search for would be ,4, (Otherwise, there's the same issue of "13" matching "3".)

One can't have both data and a formula in the same cell. If you want B4 to change in response to changes elsewhere (and don't want to write a script), then B4 must contain a formula. Then the data (such as "4, 7, 8, 5H") must be elsewhere. In my example, the data is in A4 but it could be any cell. Here is the formula for B4:

=join(", ", split(regexreplace(A4, iferror("(?i)\b("&join("|", filter(E4:H4, len(E4:H4)))&")\b", ""), ""), ", "))

Explanation:

1. iferror(join("|", filter(E4:H4, len(E4:H4))), "") joins nonempty cells in the range E4:H4 into a regular expression such as 7|abc|5H
2. The regex is wrapped in word boundaries: \b(7|abc|5H)\b, so that 7 does not get removed from 7H, for example.
3. It is made case-insensitive with the flag (?i), so that 2h is treated the same as 2H.
4. regexreplace replaces the above with empty strings
5. The split-join operation takes care of empty places left after replacement. For example, if regexreplace produced 4, 7, , 5H then split-join will make it 4, 7, 5H.