To filter a data in an array formula (to exclude or require certain values), you can use an array formula based on the IF, MATCH, and ISNUMBER functions.
In the example shown, the formula in H5:
{=COUNT(IF(ISNUMBER(...

To list the most frequently occurring numbers in a column (i.e. most common, second most common, third most common, etc), you can an array formula based on four Excel functions, IF, MODE, MATCH, and ISNUMBER. In the...

To sum if cells contain either one text string or another (i.e. contain "cat" or "rat") you can use the SUMPRODUCT function.
Background
When you sum cells with "OR" criteria, you need to be careful not to double count...

To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions.
In the example shown, the formula in xxxx is:
=SUMPRODUCT(--ISNUMBER(C5:C9))>0
How this formula works
Working...

Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that contain specific text. However, if you want more flexibility, you can use your...

To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and "*"...

Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt.
Sometimes you encounter data that mixes...

To lookup the latest file version in a list, you can use a formula based on the LOOKUP function together with the ISNUMBER and FIND functions. In the example shown, the formula in cell G7 is:
=LOOKUP(2,1/(ISNUMBER(...

To disallow input that contains one of many things, you can use a custom data validation rule based the SEARCH function.
In the example shown, the data validation applied to B5:B11 is:
=SUMPRODUCT(--ISNUMBER(SEARCH(...

To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the...

To test a cell for one of several things, and return a custom result for the first match found, you can use an INDEX / MATCH formula based on the SEARCH function.
In the example shown, the formula in C5 is:
{=INDEX(...

If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions.
Context
Let's say you...

To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 is:
=IF(F5...

To count cells that do not contain many different strings, you can use a rather complex formula based on the MMULT function. In the example shown, the formula in F5 is:
{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(...

To allow only unique numbers in a given range, you can use data validation with a custom formula based on the AND, ISNUMBER, and COUNTIF functions.
In the example shown, the data validation applied to B5:B9 is:
=AND(...