Numbers: Search spreadsheet by any word or partial string

With the search string in A1 and the data in column A of a table named 'Data' you can do this in the first cell where you want the results, in this example A2:

=FILTER(Data::A, ISNUMBER(FIND(A$1, Data::A)), "no match")

If you need case-insensitive, try SEARCH instead of FIND.

As always, you need enough empty cells below for the formula to "spill".

SG

The question mark is a wildcard in the SEARCH function, as is the asterisk. If you precede it with a ~ in your search word it will treat it as the actual character. Or you can go back to using FIND, which does not use any wildcards, and fix the case problem by converting everything to upper case.

So, to find the word "interest?" you would search for "int~?"

or

=IFERROR(FIND(UPPER(Table 2::$A$2),UPPER($A2))>0,"")

Something like this?

Filter not on:

Filter on:

Formula in B2 =IFERROR(FIND(Table 1::$A$2,A2)>0,"")

Got it. It is the blank rows in your data table getting filtered out when your search term is blank. This is just a difference in what FIND and SEARCH do under those conditions. FIND will be TRUE on those blank rows but SEARCH will be an error. When using SEARCH, those rows get filtered out. You can see what is going on if you turn the filter off.

From an earlier post of yours I thought the problem with SEARCH was question marks being interpreted as wildcards instead of characters. FIND doesn't use wildcards so no problem with it.

You can use UPPER to counteract the case sensitivity of FIND

=FIND(UPPER(Table 1::$A$2),UPPER(A2))>0

ewen256 wrote:

it wouldn't work with is a question mark (?). Can't figure out that one.

I just made one change. I used the suggestion by SGIII to change FIND to SEARCH to make it case-insensitive.

No fiddling around with extra columns, filter side panels and trying to figure out ? in the modern version.

SG

ewen256 wrote:

I'm running Numbers v12.1 on a 2014 Mac mini. Guess I'll have to splurge on a newer computer to get the latest upgrade.

After over a decade (an eternity in tech) I think you'll find many things to like in the recent hardware and software.

SG

I don't understand. I have never seen anything turn filters on/off except by me doing it myself. I suspect you have the filter ON all the time and you are seeing the result of the functions, and the filter doing what the filter is supposed to do.

You should be able to type in the search cell without anything changing with the rows being filtered out, until you hit Enter or Tab or something else that locks in the new search term. But if you hit Delete to clear out the old term first, that will change the search term to null and that will change what rows are filtered out. If you are seeing the filtered rows change after each letter you type, you'll have to explain some more. I've not seen that.

FIND along with UPPER or LOWER lets you search without case sensitivity. You just make everything upper or lower case and there is no case sensitivity to worry about.