Array Functions
Intermediate
Split a stuck-together name column
TEXTSPLIT breaks text at a delimiter into separate cells.
Task:
Payroll sent one column with first and last names in it, and the new system needs them apart. In C2, use TEXTSPLIT to split the name in A2 at the space so the first name lands in C2 and the surname in D2.
Learning Objectives:
- Split text into cells
- Use a quoted delimiter
- Recognise a horizontal spill
Hints
Solve without hints for +5 XP
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 | D | |
|---|---|---|---|---|
| 1 | Full name | Staff ID | First | Last |
| 2 | Ada Lovelace | E-104 | ||
| 3 | Bo Nilsson | E-118 | ||
| 4 | Cy Watanabe | E-133 |
What this exercise teaches (contains the answer)
Text to Columns does this once and then you do it again next month. TEXTSPLIT spills across, so it stays correct when the data is refreshed — and it is the first spill most people meet that goes sideways rather than down.