Build a padded invoice number (TEXT + &)
The problem
Problem statement
The order system stores a store code in B2 and a plain counter in A2. Finance wants invoice numbers in a fixed shape — NY-0042 — because the PDF template and the barcode both assume four digits. Fill the Invoice number cell D2.
The counter has to be padded to four digits — a plain join turns 42 into 42, and case 2 (7) would lose three zeros.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Order # | Store | Invoice number |
| 2 | 42 | ny |
Notes
Numbers do not carry leading zeros, so padding is a formatting job: TEXT(A2, "0000") turns 7 into 0007 and leaves 1023 alone. & then glues the parts together in the order you write them. UPPER exists because store codes arrive lowercase, and the invoice shape is uppercase.
How to approach
Your answer goes in D2. 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 D2
- Store code is in B2, the counter in A2
- TEXT(number, "0000") formats the counter with leading zeros
- Shape: =UPPER(?) & "-" & TEXT(?, "0000")
Official solutions
Show 2 solutions — try it yourself first
Approach 1: join with & and pad with TEXT
=UPPER(B2)&"-"&TEXT(A2,"0000")
UPPER normalises the store code, TEXT(A2,"0000") forces four digits, and the ampersands place the dash between them. Reading the formula left to right is the same as reading the invoice.
Approach 2: CONCAT with the same pieces
=CONCAT(UPPER(B2),"-",TEXT(A2,"0000"))
CONCAT takes the parts as arguments instead of operators — handy when you later want to append a range of cells. The output is identical.
Function guide
FAQ
How do I keep leading zeros in Excel?
Store the number and format it: TEXT(A2,"0000") returns text with the zeros attached. A cell holding the number 42 can never display as 0042 without a format, because the number itself has no width.
Why wrap the store code in UPPER?
The export writes store codes in lowercase, but the invoice template expects uppercase. UPPER makes the formula independent of how the source data happens to be typed.
Is & different from CONCAT?
Both join text. & reads as an operator and is shorter for a handful of pieces; CONCAT takes arguments and can take a whole range. The result here is the same.