Excel exercises
Excel exercises: Finding text
A lot of the text in a spreadsheet has to be measured or cut before it can be used.
LEN, LEFT and RIGHT give length and edges, enough to pull an order code apart. MID takes characters from the middle, and FIND and SEARCH locate a delimiter. FIND is case-sensitive, SEARCH is not, which decides whether a formula survives a typo, and wildcards in criteria find a name you only half remember. TEXTBEFORE and TEXTAFTER do the same split in one step.
Seven exercises at levels 1 to 3, and the summary asks which of these a task needs.
The hard part is the empty case. A delimiter that is missing returns an error from FIND, and LEFT built on that error fails too, so the fix is one test around the whole thing. Each exercise in the group has at least one row like that. A FIND that returns #VALUE! tells you the delimiter is missing from that row.
A stale exercise is one you finished two weeks ago or longer. One that needs practice took a hint, or more than one try.
0 of 7 complete
- LEFT + FIND: pull out a prefix Paid Pull a prefix of any length out of a code with LEFT and FIND.
- LEN, LEFT and RIGHT: count and cut Measure a string, then cut fixed pieces from either end.
- MID: characters from the middle Paid Read a fixed slice from the middle of a code, from a position you worked out.
- FIND and SEARCH: case-sensitive or not Paid Choose FIND for a case-sensitive position, SEARCH when case should not matter.
- Wildcards in text criteria Paid Write a wildcard criterion in COUNTIF and reuse the same pattern in SUMIF.
- TEXTBEFORE and TEXTAFTER: split on a delimiter Paid Split a code at its delimiter, taking either the part before or the part after.
- Text search summary Paid Combine FIND with MID for a slice, and a wildcard criterion for a filtered average.