7. Find and Replace

In real Excel, Find opens with Ctrl+F and Replace with Ctrl+H (on Mac: Cmd+F and Ctrl+H), or through Home › Find & Select. The Options button adds important settings:

• Within: the current sheet or the whole workbook;
• Look in: Formulas or Values. If a cell holds the formula =B2*2 but displays 500, only a search in Values will find "500";
• Match case and Match entire cell contents. With the second box unchecked, "coffee" also turns up inside "Coffee maker";
• Find All lists every match, which makes it easy to select all the found cells at once.

Wildcards work in the Find box and in functions such as COUNTIF, SUMIF and VLOOKUP:

• * stands for any number of any characters: *coffee* means "coffee" anywhere in the text;
• ? stands for exactly one character: ART-202?;
• ~ switches a special character off: ~* looks for an actual asterisk, ~? for a question mark.

Replacing with Ctrl+H changes your data right away and for good (until Ctrl+Z). Formulas do the same thing safely, in a neighboring column, leaving the original untouched:

• SUBSTITUTE(text, old, new) replaces a fragment by its content;
• REPLACE(text, start, count, new) replaces by position: the first 3 characters, the 5th character…;
• SEARCH(find, within) returns the position of a fragment, ignoring case (it understands * and ?); FIND is case-sensitive.

The trainer has no Find and Replace dialog, so here you do the same job with formulas. An asterisk at the end of a name marks a new product.