Next PO number from the last code (MID + VALUE)
The problem
Problem statement
Purchase orders are coded PO-<year>-<serial>, and the next one must continue the serial of the code in A2 rather than restart at 1. The serial is the last four characters. Put the next serial as a number in the Next number cell B2.
The result must be a real number, not the text "0043" — the next step in the workflow adds to it.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | |
|---|---|---|
| 1 | PO code | Next number |
| 2 | PO-2026-0042 | |
| 3 | PO-2026-0043 | |
| 4 | PO-2025-0199 |
Notes
RIGHT(A2, 4) grabs the serial and VALUE turns it into a number, because text "0042" plus 1 would be "00421". MID with two FIND calls is the more precise route: the serial starts after the second hyphen, so nothing about the code's length is assumed. Leading zeros in the code are why the text and the number are not the same value.
How to approach
Your answer goes in B2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is B2
- The serial is the last four characters of A2
- Text plus one concatenates — convert to a number first
- Shape: =VALUE(RIGHT(?, ?)) + 1
Official solutions
Show 2 solutions — try it yourself first
Approach 1: RIGHT + VALUE
=VALUE(RIGHT(A2, 4)) + 1
RIGHT(A2,4) is "0042", VALUE makes it 42, plus one is 43. Without VALUE the formula returns "00421" — the classic text-plus-number bug.
Approach 2: MID after the second hyphen
=VALUE(MID(A2, FIND("-", A2, FIND("-", A2) + 1) + 1, 4)) + 1
The inner FIND locates the first hyphen, the outer one starts after it and finds the second, so MID begins one character later. Same answer, but it survives a code where the year part changes width.
Function guide
FAQ
Why does my number come out as 421 instead of 43?
The serial is text, so adding 1 concatenates instead of adding. Wrap it in VALUE first.
How do I extract part of a code reliably?
Use FIND to locate the delimiters and MID to slice between them. Fixed positions break as soon as the year or the prefix changes width.