Array FunctionsBeginner

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

5 rows × 3 columns8 cells you fill in
ABC
1Supplier ReferencePO NumberRevision #
2PO-48213-REV2
3PO-50011-REV1
4PO-49876-REV3
5PO-51200-REV1
What this exercise teachesMay contain 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.