Text
Increment a zero-padded ID number inside a text string (INV-0099 -> INV-0100)
Isolate the trailing numeric segment of a structured code, add N, and preserve leading-zero width automatically across 99-to-100 rollovers.
Keep leading zeros intact when 0099 rolls over to 0100
Adding +1 directly to "INV-0099" causes #VALUE! because the prefix makes the cell text. Naive VALUE(RIGHT(A2, 4)) + 1 returns 100—losing the leading zero ("INV-100" instead of "INV-0100", which breaks alphabetical sorting).
Splitting at the last hyphen with TEXTBEFORE(A2, "-", -1) and TEXTAFTER(A2, "-", -1) isolates both prefix ("INV" or "LOT-2026") and digit block ("0099"), increments the number, and pads with REPT("0", MAX(0, width - LEN(nextNum))).
| CurrentID (A) | Step (B) | Next ID Result |
|---|---|---|
| INV-0099 | 1 | INV-0100 (4-digit width kept) |
| LOT-2026-0007 | 5 | LOT-2026-0012 |
| INV-9999 | 1 | INV-10000 (expands cleanly without truncating) |
Rollover-safe prefixed ID increment formula
Using REPT("0", MAX(0, LEN(numStr) - LEN(nextNum))) & nextNum preserves 4-digit padding AND lets 9999 + 1 expand to 10000 instead of clipping with RIGHT.
=LET(pfx, TEXTBEFORE(A2, "-", -1), sfx, TEXTAFTER(A2, "-", -1), nxt, VALUE(sfx) + B2, pfx & "-" & REPT("0", MAX(0, LEN(sfx) - LEN(nxt))) & nxt)Inspect the numeric suffix
TEXTAFTER shows the suffix used by the main formula. For a generated series, keep the seed reference fixed and use a deliberate increment; row-derived values change when rows move.
=TEXTAFTER(A2, "-", -1)Inputs and interpretation
Require a nonempty digits-only suffix and an integer step producing a nonnegative result. Keep numeric values within spreadsheet integer precision (about 15 significant decimal digits); this is not arbitrary-length ID arithmetic. Expanded width avoids truncation but no longer guarantees lexicographic order. This formula does not reserve unique IDs across simultaneous users; use a central allocator for that purpose.
Try the example
Use the original values or change them to test your understanding.