MID between two separators
Pull the middle section out of a code when every part varies in length.
Task:
Order references look like REGION-NUMBER-SUFFIX, and each part can be a different length. In B2:B6 extract just the number between the two hyphens.
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 LEFT, RIGHT, MID
0/4The same formula in the other shapes it takes at work.
- LEFT, RIGHT, and MID Functions
Extract parts of text using LEFT, RIGHT, and MID functions.
CoreIntermediate - LEFT up to a space: the first name
When the length changes from row to row, let FIND work it out.
Intermediate - RIGHT after a space: the last name
Count from the end: total length minus where the space is.
Intermediate - MID between two separators
Pull the middle section out of a code when every part varies in length.
Advanced
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 | |
|---|---|---|
| 1 | Reference | Order no. |
| 2 | UK-4471-B | |
| 3 | IE-90213-RTN | |
| 4 | FR-38-X | |
| 5 | DE-1150-B | |
| 6 | NL-702945-EXP |
What this exercise teachesMay contain the answer
MID needs a start and a length, and here both depend on the row. Searching for the second hyphen from just after the first is the key move. The result is text — wrap it in VALUE if you need to do arithmetic with it.