SPLIT & TEXTJOIN practice exercises
SPLIT breaks text into separate cells at a delimiter, like a dash or comma. TEXTJOIN does the opposite: it combines a range into one cell with a separator. The & operator joins individual pieces.
Syntax
SPLIT(text, delimiter) · TEXTJOIN(delimiter, ignore_empty, range) · A2&" "&B2
SPLIT(text, delimiter)- Spills the pieces into the cells to the right.
TEXTJOIN(delimiter, TRUE, range)- Joins every cell of range with delimiter between them; TRUE skips empty cells.
&- Joins two pieces of text:
A2&" - "&B2.
The practice data
Customer records with full names, emails, product codes (country-number-color) and messy city names. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | Product code | City | |
| 2 | Ana Lopez | [email protected] | US-4417-RED | new york |
| 3 | Ben Ortiz | [email protected] | CA-0932-BLU | TORONTO |
| 4 | Cara Smith | [email protected] | US-1205-GRN | chicago |
| 5 | Dev Patel | [email protected] | UK-7781-BLK | London |
| 6 | Eva Ross | [email protected] | US-3390-RED | san diego |
Worked example
=A2&" <"&B2&">"
Builds an email-style label: Ana Lopez <[email protected]>. Result:
| Ana Lopez <[email protected]> |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Join all full names (A2:A6) into one cell, separated by a comma and a space.
2 Split the product code in C2 into its 3 parts.
3 Return the username (the part before @) of the email in B3.
Master SPLIT & TEXTJOIN in the full course
Lesson 8 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon SPLIT & TEXTJOIN mistakes
- Leaving data in the cells to the right of SPLIT, which blocks the spill (#REF!).
- SPLIT treating each character of the delimiter separately. SPLIT(A2,"--") splits on every single dash unless you set the third argument to FALSE.
- Using CONCATENATE for ranges. It joins without a separator; TEXTJOIN is usually what you want.
FAQ
Does Excel have SPLIT?
No. Excel 365 has TEXTSPLIT, and older Excel uses Text to Columns. TEXTJOIN exists in both.
How do I add a line break between joined items?
Use CHAR(10) as the delimiter and turn on text wrapping.