Practice by topic / Excel / Text Functions
FIND locates the space (or comma, or @), LEFT takes the text before it and MID or RIGHT the text after: =LEFT(A2,FIND(" ",A2)-1) returns the first name and =MID(A2,FIND(" ",A2)+1,100) the last. The same pattern splits email addresses, codes and "Last, First" lists. These 10 exercises build up step by step, starting with get the first name, get the last name with right, get the last name with mid.
3 of 10 free · no sign-up needed · graded on the formula you write
A2 holds a full name. Write a formula in B2 that returns the first name (everything before the space).
| A | B | |
|---|---|---|
| 1 | Full name | |
| 2 | Sarah Lim | ? |
Use =LEFT(A2,FIND(" ",A2)-1)
Right — FIND gives the position of the space, and LEFT takes everything before it.
Expected result in B2: Sarah
A2 holds a full name. Write a formula in C2 that returns the last name (everything after the space) using RIGHT, LEN and FIND.
| A | B | C | |
|---|---|---|---|
| 1 | Full name | ||
| 2 | Sarah Lim | ? |
Use =RIGHT(A2,LEN(A2)-FIND(" ",A2))
Good — the total length minus the position of the space is the number of characters in the last name.
Expected result in C2: Lim
A2 holds a full name. Write a formula in C2 that uses MID to return everything after the space.
| A | B | C | |
|---|---|---|---|
| 1 | Full name | ||
| 2 | Daniel Wong | ? |
Use =MID(A2,FIND(" ",A2)+1,100)
Right — start one character after the space and ask for more characters than you'll ever need.
Expected result in C2: Wong
A2 holds a name written as 'Last, First'. Write a formula in B2 that returns the last name (everything before the comma).
| A | B | |
|---|---|---|
| 1 | Name (Last, First) | |
| 2 | Tan, Mei Ling | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2 holds a name written as 'Last, First'. Write a formula in C2 that returns the first name (everything after the comma and the space that follows it).
| A | B | C | |
|---|---|---|---|
| 1 | Name (Last, First) | ||
| 2 | Tan, Mei Ling | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2 holds an email address. Write a formula in B2 that returns the part before the @ sign.
| A | B | |
|---|---|---|
| 1 | ||
| 2 | [email protected] | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2 holds an email address. Write a formula in C2 that returns the domain (everything after the @ sign).
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | [email protected] | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2 holds a first and last name. Write a formula in B2 that returns the initials, e.g. 'SL'.
| A | B | |
|---|---|---|
| 1 | Full name | |
| 2 | Sarah Lim | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2 holds a name written as 'Last, First'. Write a formula in B2 that returns it as 'First Last'.
| A | B | |
|---|---|---|
| 1 | Name (Last, First) | |
| 2 | Lim, Sarah | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2 holds a name with extra spaces and no capitals. Write a formula in B2 that returns the first name in proper case.
| A | B | |
|---|---|---|
| 1 | Full name (as typed) | |
| 2 | ahmad ismail | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →