Recently, a consulting client called to ask the following Excel question: How can I find all the records that contain a particular word in a certain column?
My first reaction was to recommend Excel’s AutoFilter feature. Go to Data | Filter, and then click on the dropdown arrow for the appropriate column and choose the appropriate value—or in this case, the appropriate word. Excel will filter out all records except those that contain that word.
That solution would have worked except for one crucial detail: The column in question contained long text labels, not single-word entries. The client wanted to locate all the records where the key word appeared anywhere within those text labels. Furthermore, the key word might appear with an initial capital or in all lowercase letters.
Fortunately, Excel provides all the tools needed to ferret out the records that contain the key word. Here’s the solution I recommended.
Find, flag, and sort
I first introduced you to Excel’s Find function in “Using Excel's Find and Mid to extract a substring when you don't know the start point.” In that case, our goal was to locate a substring and extract it. In this case, we’ll use the Find function simply to “flag” the records that contain a particular key word. Once those records are identified, the client can copy those rows out to another worksheet.
The solution I proposed was to use the Find function, which takes the form
In this case, the client wanted to find all the records in which the word “sensitive” appears in the label. If the word “sensitive” had been entered in all lowercase letters, we could have used the formula =Find(“sensitive”,A2), assuming the source labels start in cell A2.
Another wrinkle was the fact that, in some of those labels, “sensitive” was the first word in the label and was initial capped. To address that problem, we simply wrapped the Lower function around the cell reference. (The Lower function converts a given string to all lowercase letters.) So our formula took the form:
Figure A shows what our sample sheet looked like after we copied our formula. For labels that do contain our keyword, the Find function returns an integer corresponding to the word’s position within the string. Notice that, for labels that do not contain the key word “sensitive,” the Find function returns the #VALUE! error message.
|If the Find function locates an occurrence of the specified string, it returns an integer; otherwise, it returns an error message.|
We could modify our formula so that it returns a null string instead of the error message. However, in this case, we count on the error message to identify records that we want to exclude from our search. So, we simply sort our records primarily by the Is Found column, and, as Figure B shows, all of the records we want—the ones with integers in the Is Found column—get sorted to the top of the list.
|By sorting our records by the Is Found column, all of the rows that contain our key word move to the top of the list.|
If your Excel users are like my client, they’ll learn to love using the Find function to flag records that contain a particular string in a given column. In future tips, we’ll learn some other techniques for finding subsets of Excel records, including how to set up criteria ranges for use with Excel’s Advanced Filter tool.
What’s your favorite Excel trick?
To comment on this tip or to share your favorite spreadsheet solution, start a discussion below or write to Jeff.