Splitting and joining: practice exercises
LEFT, MID, FIND, TEXTAFTER, TEXTJOIN. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In B2, take the first name out of each full name: everything before the space.
| A | B | |
|---|---|---|
| 1 | Full name | First name |
| 2 | Hamza Bennett | |
| 3 | Jordan Taylor | |
| 4 | Sunil Hughes | |
| 5 | Tom Brown | |
| 6 | Ella Walker | |
| 7 | Will Johnson | |
| 8 | Tanya Baker | |
| 9 | Ollie Hall | |
| 10 | Liam Turner | |
| 11 | Marek Mensah |
How it works
FIND(" ",A2) gives the position of the first space. Everything before it is the first name: =LEFT(A2,FIND(" ",A2)-1). The -1 leaves the space out. Everything after it starts one place later: =MID(A2,FIND(" ",A2)+1,50), where 50 just means "the rest".
TEXTBEFORE and TEXTAFTER do the same jobs more simply, in newer versions of Excel and in Google Sheets. TEXTJOIN(", ",TRUE,range) joins a range with a separator, and TRUE skips blank cells.
=LEFT(A4,FIND(" ",A4)-1) =MID(A4,FIND(" ",A4)+1,50) =TEXTBEFORE(B4,"@") =TEXTJOIN("; ",TRUE,A2:A4)
Finding the split point
FIND(" ",A2) gives the position of the first space. In "Amira Khan" that's 6.
- The first name is the 5 characters before it:
LEFT(A2, FIND(" ",A2)-1). - The surname starts one after it:
MID(A2, FIND(" ",A2)+1, 50). 50 just means "plenty", to take the rest.
Use FIND with whatever separates the parts: a space, a comma, an @.
Worked example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | First | Last | Domain | |
| 2 | Amira Khan | amira.khan@northgate.co.uk | |||
| 3 | Ben Ode | ben.ode@kestrel.org | |||
| 4 | Cara Lin | cara.lin@oakfield.com |
=LEFT(A2,FIND(" ",A2)-1)in C2. Everything before the space: Amira, Ben, Cara.=MID(A2,FIND(" ",A2)+1,50)in D2. Everything after the space: Khan, Ode, Lin.=TEXTAFTER(B2,"@")in E2. TEXTAFTER does the same job in one step: everything after the @ is the domain, northgate.co.uk.
Joining a list
& joins two or three things. To join a whole range, use TEXTJOIN(separator, skip blanks, range):
=TEXTJOIN(", ",TRUE,A2:A4) gives "Amira Khan, Ben Ode, Cara Lin". TRUE means empty cells are left out, so you don't get ", ," in the middle.
What you'll practise
- In B2, take the first name out of each full name: everything before the space.
- In B2, take the surname out of each full name: everything after the space.
- In C2, take the domain out of each email address: everything after the @.
- Some shifts aren't covered yet. In D2, make one list of everyone covering a shift this week, separated by a comma and a space, with no gaps for the empty shifts.