About this exercise
A 150-row dataset on one sheet, 2 answer cells, about 7 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- C2
=LEFT(B2,FIND("@",B2)-1) - D2
=RIGHT(B2,LEN(B2)-FIND("@",B2))
FIND("@",B2) returns the position of the @ sign as a number, which turns a variable-length text into something LEFT and RIGHT can work with. LEFT(B2,FIND("@",B2)-1) keeps everything before the @ (one character fewer than its position), and RIGHT(B2,LEN(B2)-FIND("@",B2)) keeps the characters after it — the total length minus the position of the @. TEXTBEFORE and TEXTAFTER wrap this logic into one function each, but they exist only in Excel 365.
Pitfall 1: FIND is case-sensitive and SEARCH is not; for a single @ either works, but SEARCH also accepts wildcards (? and *), which bites when the delimiter is a literal question mark. Pitfall 2: an address without an @ makes FIND return #VALUE!, which propagates into LEFT and RIGHT; wrap the formula in IFERROR(…,"") when the export may contain phone numbers or blanks. Pitfall 3: a hard-coded count such as =LEFT(B2,12) passes on one row and fails on the next — the grader changes B2 and expects the split to follow. MID(B2,FIND("@",B2)+1,255) is a shorter way to say "everything after the @": asking for more characters than exist is harmless. In Excel 365, =TEXTSPLIT(B2,"@") returns both parts as a spilled array; to put each part in its own column use INDEX(TEXTSPLIT(B2,"@"),1) and INDEX(TEXTSPLIT(B2,"@"),2), because a spilled result cannot be filled down and leaves the second column without a formula of its own. Text to Columns (Data tab, delimiter "@") and Flash Fill (Ctrl+E) split interactively but produce static values; formulas keep following the source. Case: these functions preserve the capitalisation of the source; add LOWER() around the domain before counting domains, otherwise Gmail.com and gmail.com are two groups.
Other ways that work
- =TEXTBEFORE(B2,"@") and =TEXTAFTER(B2,"@") — Excel 365 only
- =MID(B2,FIND("@",B2)+1,255) — the domain via MID; the 255 just means "to the end"
- =SUBSTITUTE(B2,C2&"@","") — the domain by removing the user part already computed in C2
- =INDEX(TEXTSPLIT(B2,"@"),1) / =INDEX(TEXTSPLIT(B2,"@"),2) — Excel 365, one part per column
Common mistakes
Counting characters instead of finding the @: =LEFT(B2,10) returns the right user for B2 and the wrong one for every other row — the position of the @ changes with each address.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.