Level 3 · Codes, slugs and keyword flags

Andrew’s ticket is three columns, each built from text that is already in the sheet. The trick in all three is not a new function but functions inside functions. Excel evaluates the innermost one first and hands its answer outward, which lets you do in one cell what would otherwise take two or three helper columns.

A code from part of a column

The category in C2 is Textiles. Andrew wants tex-161:

D2   =LOWER(LEFT(C2,3))&"-"&A2

Read it from the inside: LEFT(C2,3) is Tex, LOWER turns it into tex, then & adds a hyphen and the product_id. Standardising case is the point of LOWER here: if one export says TEXTILES and another Textiles, both still give tex, and codes that should match do match. Decor products get dec-181 and so on.

A slug with SUBSTITUTE

SUBSTITUTE(text, old, new) replaces every occurrence of old with new:

E2   =LOWER(SUBSTITUTE(B2," ","-"))

Cove Jute Duvet Cover becomes cove-jute-duvet-cover. Row 6, Weston Cashmere-Blend Quilt, already has a hyphen; it stays, and the slug is weston-cashmere-blend-quilt. SUBSTITUTE takes an optional fourth argument to replace only one occurrence: SUBSTITUTE(B2," ","-",1) changes the first space only.

The same nesting cleans free text such as review comments. =TRIM(LOWER(review)) lowercases the text, removes spaces at both ends and collapses runs of spaces inside it to one, and wrapping that in SUBSTITUTE(…,"!!","!") fixes a habit Excel has no function for. Chapter 5 is all about that kind of cleaning.

Does the name contain a word?

SEARCH(find, within) returns the position where find starts, or #VALUE! when it isn’t there. ISNUMBER turns that into TRUE or FALSE, and IF into the words you want:

=IF(ISNUMBER(SEARCH($I$1,B2)),"yes","no")

It works on row 25, Thatcher Oak Candle. Now look at row 3: B3 is Oakley Jute Knit Throw, and oak is inside Oakley, so the formula says “yes” for a throw made of jute. The same happens in row 40, Oakley Brass Framed Print.

The fix is to search for the word with a space on each side, inside the name with a space added at each end, so a word at the start or the end of the name still has spaces around it:

F2   =IF(ISNUMBER(SEARCH(" "&$I$1&" "," "&B2&" ")),"yes","no")

Now " oak " is found in " Thatcher Oak Candle " but not in " Oakley Jute Knit Throw ". Filled down, 4 of the 40 products get “yes”. Change I1 to brass and the column follows: 8 products.

FIND or SEARCH

	FIND	SEARCH
Upper and lower case	must match: FIND("oak","Oak") is #VALUE!	ignored
Wildcards * and ?	no	yes

The names say Oak and the keyword cell says oak, so SEARCH is the right one. Use FIND when case matters, for example to tell the code ABC from abc.

Common mistakes

• No ISNUMBER. =IF(SEARCH(…),"yes","no") returns #VALUE! on every row without the word, because the error flows through IF.
• Partial words. oak matches Oakley, and elm would match Helmsley. Pad with spaces when you mean a whole word.
• LOWER on the wrong piece. =LEFT(LOWER(C2),3) works too, but =LOWER(LEFT(C2),3) has the 3 in the wrong function: count the brackets.
• Typing the keyword into the formula. Keep it in I1 and lock it, so the next campaign is one cell away.