Split a code the export never split
The middle piece is the hard one, because both of its edges move.
Task:
The stock-take scanner writes the depot, the item number and the colour into one code separated by hyphens, and not one of the three is a fixed length. Put the depot in column B and the item number in column C.
Learning Objectives:
- Locate a delimiter with FIND rather than counting characters
- Reach a second delimiter using FIND's start-position argument
- Turn two positions into the length MID needs
Solve without hints for +5 XP
Interactive Spreadsheet
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 | |
|---|---|---|---|
| 1 | Scanned code | Depot | Item number |
| 2 | GLW-4471-BLU | ||
| 3 | MAN-88-CHR | ||
| 4 | BRSTL-901-MTT |
What this exercise teaches (contains the answer)
FIND hands back a position, and a position is a number you can do arithmetic on. That is what makes this survive a five-letter depot: nothing in the formula says "three characters", it says "up to wherever the hyphen turned out to be". The middle segment is the awkward one because it is pinned on both sides, so it needs two positions rather than one — and the only way to reach the second hyphen is to tell FIND to start looking past the first, which is exactly what its third argument is for. Subtracting the two positions gives the distance between the hyphens, and the extra -1 drops the hyphen itself out of the answer. MID returns text, so "88" here is a label rather than a quantity; wrap it in VALUE the day you need to add these up.