Filter multiple columns using "text filter | contains" without VBA?

[TheDougmeister]

Hi. Pretty sure I've seen VBA code to do this, but is it possible to filter multiple columns within a table using wildcards (e.g., "contains")?

Example column headings would be "FathersName", "MothersName","PaternalGrandparentsNames".

 

[ISY]

Hi

FILTER with partial match

To use select records from a set of data based on a partial match, you can use the FILTER function with the SEARCH function. In the example shown, the formula in G7 is: =FILTER(Table1,ISNUMBER(SEARCH(H4,Table1[Last]))*(H4""),"No results") where Table1 is an Excel Table that contains 100 rows of...

exceljet.net

 

[TheDougmeister]

That says "FILTER with partial match". Will it also search multiple columns for the same sub-string?

I.e., I want to search columns A, C, and E for the sub-string "XYZ".