Confident Cells

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.

Your turn

In B2, take the first name out of each full name: everything before the space.

AB
1Full nameFirst name
2Hamza Bennett
3Jordan Taylor
4Sunil Hughes
5Tom Brown
6Ella Walker
7Will Johnson
8Tanya Baker
9Ollie Hall
10Liam Turner
11Marek 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

ABCDE
1NameEmailFirstLastDomain
2Amira Khanamira.khan@northgate.co.uk
3Ben Odeben.ode@kestrel.org
4Cara Lincara.lin@oakfield.com
  1. =LEFT(A2,FIND(" ",A2)-1) in C2. Everything before the space: Amira, Ben, Cara.
  2. =MID(A2,FIND(" ",A2)+1,50) in D2. Everything after the space: Khan, Ode, Lin.
  3. =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

  1. In B2, take the first name out of each full name: everything before the space.
  2. In B2, take the surname out of each full name: everything after the space.
  3. In C2, take the domain out of each email address: everything after the @.
  4. 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.