Finding tools

Searching in Excel is not only the Ctrl+F button, but a whole system of tools, functions and techniques. To choose the right search method, it’s important to understand what exactly you are solving. Any search has several parameters:

• What we look for: a single character, a set of characters, a word, a list of words, a phrase, a substring, a regular expression or even elements of a certain format (bold font, color, borders). See find text characters, find words, find bold text.
• Where we look: cell contents, formulas, ranges, individual sheets or entire workbooks. More in find cells by condition.
• The search direction: top to bottom, right to left, from the end of a string.
• The search method: functions (SEARCH, FIND, VLOOKUP, COUNTIF), procedures (for example, search by several conditions), tools (filters, conditional formatting, Find and Replace) and regular expressions.
• Where to output the result: a dialog box, as in the standard search, or separate cells (formulas, macros, add-ins).
• The output format: TRUE/FALSE to check whether the item is there, a number of matches (like COUNTIF returns), or the value itself (like VLOOKUP or INDEX+MATCH).
• What we do with the source data: leave it as is, delete what we found, or delete the whole item if the search term is found in it.

Typical search scenarios

• Find all digits in text: find numbers.
• Find capital letters: find uppercase letters.
• Find repeated words: words repeated inside cells.
• Find special characters and wildcards: the list of special characters.

Useful tips

• Use keyboard shortcuts to speed up the standard search.
• For complex patterns, use regular expressions.
• If you need not only to find but also to replace in bulk, the Find and Replace article above covers that too.

Objects to find with !SEMTools find tools

With !SEMTools, you can find 6 types of objects in Excel. Each has its own submenu:

• Find characters: check whether cells contain specific characters.
• Find words: check whether cells contain the words you want.
• Find phrases: find both words and word combinations. Searching for place names is a classic example.
• Find cells: the conditions apply to the whole cell content.
• Find duplicates: in fact, this is also a search for cells, but these procedures are so popular and specific that they have their own menu.
• Find worksheets: these tools help navigate large workbooks and switch between sheets quickly.

As a rule, the find tools come before the macros of other groups, DELETE, CHANGE and EXTRACT, which is why they are placed at the very beginning.

How to use the find tools

Like most !SEMTools instruments, these work with the selected cells, except for the Worksheets menu. The algorithm is simple: copy the source range to a neighboring column (or tick “Output to the next column” in the first group of the tab), type the pattern or select the list of cells with what you want to find, and click.

The result is always TRUE or FALSE. It’s handy when you need a quick first look at the data and don’t plan any further actions yet. Set a filter on the result column to see the found (TRUE) or not found (FALSE) values at once.

Find tools use cases

Say we know in advance that all cells must contain certain data. We check the selected range, the procedure returns TRUE or FALSE, and if some cells return FALSE, we see where the error is.

Or the other way round: we know that certain data must not be in the cells. We do the same check, and if we find it, we know what to fix.

Fast-pivoting TRUE-FALSE results

Before doing anything with the found data, it’s useful to see how many cells match or don’t match your criteria. To do this, build a pivot table on the TRUE/FALSE column: it shows the scale of the problem, if something is wrong with the data.