Array Functions
Beginner
TEXTBEFORE: the part before the @
The mirror image of TEXTAFTER.
Task:
The new chat tool uses the part of each email before the @ as a username. In C2:C6 extract it.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of TEXTBEFORE, TEXTAFTER
0/4The same formula in the other shapes it takes at work.
- Get the domain out of an email address
TEXTAFTER returns everything past a delimiter.
CoreIntermediate - TEXTBEFORE: the part before the @
The mirror image of TEXTAFTER.
Beginner - TEXTAFTER the last delimiter: a file name
A negative instance number counts from the end.
Intermediate - TEXTAFTER the second delimiter
The instance number picks which hyphen to split after.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Username | |
| 2 | Ines Moreau | ines.moreau@harbour.co | |
| 3 | Jon Price | jprice@harbour.co | |
| 4 | Kemi Ade | kemi.ade@harbour.co | |
| 5 | Lars Berg | lberg@harbour.co | |
| 6 | Mo Haddad | m.haddad@harbour.co |
What this exercise teachesMay contain the answer
LEFT(B2,FIND("@",B2)-1) gets the same result with an off-by-one waiting to happen. TEXTBEFORE says what it means — the text before the @ — and needs no arithmetic at all.