StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is pretty easy to do with just formulas, as long as you don't care too much about possibly improperly finding parts of words. Ignore that caveat for a second though. First, here is a formula that will tell you if any of several strings are found anywhere within a source string:</p> <pre><code>=OR(NOT(ISERROR(FIND(<array of strings to look for>,<string to look in>)))) </code></pre> <p>This needs to be entered as an array formula for it to work. You do that by entering it with Ctrl-Shift-Enter. To understand how it works, consider what Excel does in evaluating a real example:</p> <pre><code>=OR(NOT(ISERROR(FIND({"a","b","c"},"q b q")))) </code></pre> <p>'FIND' finds the position of one string within another. When called with an array for the first argument, it will return an array of positions (or #VALUE! if the search string isn't found). You can trace the evaluation by entering that formula and then using the F9 key on expressions within it:</p> <pre><code>=OR(NOT(ISERROR({#VALUE!,3,#VALUE!}))) =OR(NOT({TRUE,FALSE,TRUE})) =OR({FALSE,TRUE,FALSE}) =TRUE </code></pre> <p>So, for your example, say you had your strings you want searched in $B$6:$B$8, your work strings in $D$2:$D$3, and your play strings in $E$2:$E$3. You could put the formula</p> <pre><code>=OR(NOT(ISERROR(FIND(D$2:D$3,$B6)))) </code></pre> <p>in cell D6, enter it as an array formula, and then drag it through the range D6:E8 to find which strings in B had work or play words in them. Then you could use those results to drive further formulas or conditional formatting.</p> <p>However, as mentioned above, you'll note that any substring within the string being searched will get found, so </p> <pre><code>=OR(NOT(ISERROR(FIND({"a","b","c"},"bad")))) </code></pre> <p>will evaluate to TRUE. (And if your fun list includes "id", the "id" in "davids" will match.)</p> <p>As is often the case with Excel, if you're doing something you understand with a limited data set, you might not care about this. But it can defeat an attempt to use this kind of formula as part of a general "application" that has users who don't know fancy array tricks or exactly what 'FIND' does. (You can sort of get around that by putting a space after your search words, etc., but that is just more mysterious voodoo waiting to be broken if you hand it someone else.) For a quick and dirty scan, though, it's fine.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload