Nested Text Functions
Real text rarely comes in a fixed shape. These five exercises combine SEARCH, MID, LEFT, IFERROR, and INDEX/MATCH to parse and restructure text whose pieces move around.
The Text functions page covers LEFT, MID, and SEARCH on their own, each pulling a fixed, predictable piece out of a string. Real data is rarely that tidy: an email's domain starts at a different position in every row, a name might or might not need reformatting, a search might come back empty. This set is the hardest tier of text work in the app for exactly that reason, nesting a position-finder like SEARCH into whatever extracts or decides based on that position.
Five free exercises, graded instantly, no signup required:
Marketing wants just the domain from each signup's email, not the whole address. Extract the domain from "maria.santos@brightpath.io".
Approach: The @ isn't always in the same spot, so find it first with SEARCH, then use that position with MID (asking for more characters than remain just returns the rest of the string): =MID(A2,SEARCH("@",A2)+1,LEN(A2)).
SEARCH finding a moving position, then MID/RIGHT using that position instead of a typed-in number, is how you parse text whose pieces aren't a fixed length.
Support tickets are coded by their first letter: "B" for Billing, "T" for Tech, "A" for Account, anything else is General. Resolve the department for ticket "T-8841".
Approach: Pull the leading letter with LEFT(A2,1), then nest that inside IF exactly the way you'd nest a lookup: =IF(LEFT(A2,1)="B","Billing",IF(LEFT(A2,1)="T","Tech",IF(LEFT(A2,1)="A","Account","General"))).
Nested IF wrapped around LEFT is the standard way to turn a code's prefix into a readable category, three or four branches deep.
Check whether this product's description mentions "Clearance" anywhere in the text, without adding a helper column: "Winter Jacket - Clearance Pricing".
Approach: SEARCH returns a position if it finds the word, or an error if it doesn't. Wrap it in IFERROR to turn that error into a plain 0, then compare: =IF(IFERROR(SEARCH("Clearance",A2),0)>0,"On Sale","Regular").
Excel has no simple built-in "contains" test. IFERROR catching SEARCH's error is the standard workaround, and you'll reuse this exact pattern constantly.
Each order code's first 2 letters is a region code. Pull that out of "EU-77291" and use it to find the matching warehouse.
Approach: Pull the region out with LEFT(E1,2), then feed that straight into INDEX/MATCH instead of typing the code by hand: =INDEX(B2:B4,MATCH(LEFT(E1,2),A2:A4,0)).
INDEX/MATCH doesn't care where its lookup value comes from. Passing it the output of LEFT is what makes a formula dynamic instead of hard-coded.
This roster stores names as "Last, First". Reformat "Nakamura, Haruto" into "First Last" for the mailing list.
Approach: Find the comma once with SEARCH, then use that same position twice (MID for what comes after it, LEFT for what comes before it) and join them with CONCATENATE: =CONCATENATE(MID(A2,SEARCH(", ",A2)+2,LEN(A2))," ",LEFT(A2,SEARCH(",",A2)-1)).
Restructuring text usually means finding a separator's position once, then reusing that position on both sides of it. The same SEARCH result drives two different extractions.
Why this combination
SEARCH finding a moving position, then MID/RIGHT using that position instead of a typed-in number, is how you parse text whose pieces aren't a fixed length. Working through all five in this set is what makes the pattern stick, not just the first one.
More practice groups
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice Nested Text Functions →