Split supplier PO references into order number and revision count
TEXTBEFORE and TEXTAFTER cut a string at a delimiter you name once, instead of a LEFT/RIGHT/FIND stack rebuilt for every format a supplier feed throws at you.
Task:
Procurement reconciles supplier order confirmations against the ERP every morning. The EDI feed sends each one as a single reference like PO-48213-REV2 — the purchase order number and how many times it's been revised, joined by a "-REV" the feed always uses. The nightly import needs those two pieces in separate columns. In B2, use TEXTBEFORE to pull the order number out of A2, and in C2, use TEXTAFTER together with VALUE to turn the revision tag into a number. Copy both down to row 5.
Learning Objectives:
- Split text around a shared delimiter with TEXTBEFORE and TEXTAFTER instead of stacking LEFT, RIGHT, FIND and LEN
- Recognize that TEXTAFTER returns text even when it looks like a number, and convert it with VALUE when the result needs to behave like one
- Reuse one delimiter argument to take both halves of a value apart
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 | Supplier Reference | PO Number | Revision # |
| 2 | PO-48213-REV2 | ||
| 3 | PO-50011-REV1 | ||
| 4 | PO-49876-REV3 | ||
| 5 | PO-51200-REV1 |
What this exercise teaches (contains the answer)
TEXTBEFORE(A2,"-REV") searches PO-48213-REV2 for the delimiter "-REV", finds it right after 48213, and returns everything to its left: PO-48213. TEXTAFTER(A2,"-REV") does the mirror image, returning everything after that same delimiter — the digit 2 — but as the text "2", because TEXTAFTER never inspects what it returns, only where it cuts. Wrapping it in VALUE(...) is what turns that text into the number 2, which is why the revision column is VALUE(TEXTAFTER(...)) rather than TEXTAFTER(...) alone. Both functions read the same delimiter, so a formula that split on "-REV" once does not need a second, differently-spelled version for the other half.