Google-sheets – Filter View, Text Starts With in Custom Formula

google sheets

In Google docs (spreadsheet) under Filter View, I am trying to write a custom formula for multiple conditions. For example, in column O, I may have "Jury Room" and also "Jury Rooms" and so I am trying to filter for anything beginning with just "Jury." Under the predefined conditions there is "Text Starts With" which would serve my purpose but I also want to search for other items in the same filter condition.

Best Answer

You can do this with a regexmatch function using the custom formula option under the filter by condition options instead of starts with:

=regexmatch(A:A,"^Jury|^Something")

Explanation:

in regexes: the ^ means beginning of string/line and the | is the OR operator for regexes

Related Solutions

Google-sheets – Cross referencing two sheets and pulling in values

This is what the command VLOOKUP is for.

Put in C1 =VLOOKUP(A1, Sheet2!A:C, 3, FALSE) and drag down the row. Here,

A1 is the entry to search for
Sheet2!A:C is the range in which the first column (A) is to be searched
3 is the column number from which to take the result (that is, C)
FALSE requires exact match, and does not assume the keys are sorted in the second sheet. If they are, consider omitting this (after reading the docs), but then if there is no match, you'll get a neighboring value instead of #N/A error.

You can also make the formula expand automatically as new data are added, with the help of ARRAYFORMULA:

=ARRAYFORMULA(IF(LEN(A1:A), VLOOKUP(A1:A, Sheet2!A:C, 3, FALSE), ""))

The logic is same as above, except there's a condition saying that empty cells in A column shouldn't be passed to VLOOKUP.

Google Sheets – How to Use ‘Join’ Without Excess Delimiters

I describe two ways to do this.

Using Filter command

You can join while ignoring empty cells with

=JOIN("~", FILTER(B3:E3, LEN(B3:E3)))

The command FILTER selects only the entries with nonzero value of LEN, i.e., nonempty one.

For non-contiguous input range, such as g3, f3, h3, i3, you can use {array} syntax:

=JOIN("~", FILTER({g3, f3, h3, i3}, LEN({g3, f3, h3, i3})))

Using Join-Split-Join

The previous method requires the range to be entered twice, which can be annoying in the second situation above. Here is another method:

=JOIN("~", SPLIT(JOIN(CHAR(60000), B3:E3), CHAR(60000)))

First, it joins using a private use Unicode codepoint (which does not correspond to any standard character and therefore should not be in the input). Then it splits by the same codepoint. The command Split omits blank cells in its output, which can be annoying in other contexts, but helps here. Then the final Join works as intended.

With this method, the range only has to be entered once.