Pull a payment term off the end of a purchase order number with a varying number of segments
TEXTBEFORE and TEXTAFTER normally split at the first delimiter they meet, but a negative instance counts from the end instead — the only way to isolate a trailing segment when the segments ahead of it don't always number the same.
Task:
You process purchase orders for a manufacturing company. Each PO number ends with a payment term code, but the segments in front of it are not consistent — some carry a region code, some don't — so splitting at the first hyphen lands in a different place from one PO to the next. In column B, use TEXTBEFORE to pull everything before the payment term. In column C, use TEXTAFTER to pull the payment term itself. Fill in rows 2 through 6.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve 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.
| A | B | C | |
|---|---|---|---|
| 1 | Purchase Order | PO Reference | Payment Term |
| 2 | PO-8842-NET30 | ||
| 3 | PO-9103-US-NET45 | ||
| 4 | PO-7765-EU-COD | ||
| 5 | PO-8820-NET60 | ||
| 6 | PO-9344-APAC-NET30 |
What this exercise teachesMay contain the answer
TEXTBEFORE(A2,"-",-1) and TEXTAFTER(A2,"-",-1) don't count hyphens from the front — the -1 tells them to count from the back, so it always means the last one, regardless of how many came before it. PO-8842-NET30 has a single hyphen ahead of its payment term and splits cleanly into PO-8842 and NET30. PO-9103-US-NET45 has two, and the same formula in row 3 still finds NET45 after the last one, carrying PO-9103-US along as the reference rather than stopping at the first hyphen the way an instance of 1 would, which would have split it into PO-9103 and US-NET45 instead. A formula built on counting from the front would need to know in advance how many segments each PO number carries; one built on counting from the back never has to.