TEXTSPLIT a code into its parts
One formula per row; each spills across as many columns as there are parts.
Task:
SKU codes pack the category, size and colour into one cell, separated by hyphens. In B2:B5 split each code so the three parts land in B, C and D.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of TEXTSPLIT
0/3The same formula in the other shapes it takes at work.
- Split a stuck-together name column
TEXTSPLIT breaks text at a delimiter into separate cells.
CoreIntermediate - TEXTSPLIT a code into its parts
One formula per row; each spills across as many columns as there are parts.
Intermediate - TEXTSPLIT on a comma and a space
A delimiter can be more than one character, so no stray spaces are left behind.
Intermediate
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 | SKU | Category | Size | Colour |
| 2 | TEE-M-BLK | |||
| 3 | HOOD-L-GRY | |||
| 4 | CAP-OS-NVY | |||
| 5 | SOCK-S-WHT |
What this exercise teachesMay contain the answer
The old way was three formulas per row — LEFT up to the first hyphen, MID between them, RIGHT after the last — each one a FIND exercise. TEXTSPLIT does the whole job in one, and copes when a part, like OS, is a different length.