Pad Invoice Numbers with Leading Zeros in Excel and Sheets

Your invoice register has INV-1, INV-2, INV-10, INV-100. Sort ascending: you get INV-1, INV-10, INV-100, INV-2 — alphabetical, not numeric. Your reconciliation formulas that match on invoice number find no match for INV-001 against INV-1. You need every invoice number to be the same width: INV-00001, INV-00010, INV-00100.

The Fix — TEXT Formula

If the raw value is a number (1, 2, 10, 100) and you want to display it as INV-00001:

="INV-"&TEXT(A2, "00000")

TEXT(A2, "00000") converts the number to a string padded to 5 digits. Prefix with "INV-" and you’ve got your formatted invoice number. Drag down the column.

If the raw value is already INV-1 and you need to re-pad:

="INV-"&TEXT(VALUE(MID(A2, 5, 10)), "00000")

MID(A2, 5, 10) grabs everything after the INV- prefix. VALUE() converts it to a number. TEXT(..., "00000") re-pads. You get a clean width-5 output regardless of the input’s padding.

The Fix — Custom Cell Format (No Formula)

If you want the underlying values to stay as plain numbers (1, 2, 10, 100) but display padded, use a custom cell format:

• Excel: Ctrl+1 > Custom → paste "INV-"00000 (with the quotes around INV-).
• Google Sheets: Format > Number > Custom number format → paste the same: "INV-"00000.

Now cell A2 contains 1 but displays INV-00001. Sorts numerically. Matches other numeric values. Exports to CSV as INV-00001 (Sheets) or as 1 (Excel — pick the right tool for your downstream).

Custom format beats the TEXT formula when:

• You want the raw value to stay numeric for SUM, MIN, MAX, etc.
• You don’t want a helper column.
• You want the format to survive re-entry (TEXT outputs get overwritten if someone types in the cell).

TEXT formula beats custom format when:

• You need the padded string as an actual value (for CSV export, or for downstream formula matching).
• The source is mixed types (some already-prefixed, some raw numbers) and you need one normalized output.

Gotchas

• Leading zeros are stripped on CSV import. If your invoice register is CSV, Excel auto-converts 00001 back to 1 on import. Force text: in the import dialog, set the invoice column type to Text (Excel) or wrap-in-apostrophe before paste.
• Padding width drift. Pick a width (5 digits = up to 99,999 invoices) and stick with it. If you outgrow the width, expanding from 5 to 6 breaks every historical match until you re-pad. Size up at ingest.
• The “INV-” prefix is brittle. Platforms sometimes strip non-numeric characters from payment references. Payment arrives referencing 00001 without the prefix. Reconciliation should match on the numeric tail, not the full string. See Reconcile Payments Against Invoices for the full join-key discussion.

If You Need the Full Workflow

Reconcile Payments Against Invoices — covers the invoice-to-payment matching end to end, including how to normalize invoice IDs before the join.

Download

The Payment Reconciliation template ships with an Invoice # column pre-formatted as INV-00000 and a helper column that normalizes inputs of any prefix/padding variant to the canonical form for joining.