Turning 7 into 00007 so a column of reference codes lines up and sorts properly.
The formula
=RIGHT("00000"&A2,5) in column B
What each part does
"00000"&A2
Five zeros added to the front, whatever the length of the number.
RIGHT(...,5)
The last five characters, which leaves exactly the padding needed.
How it works, and what to watch for
Excel drops leading zeros from numbers because they carry no value. To keep them, the result has to be text, which is what this formula produces.
Sticking five zeros on the front and taking the last five characters gives the same width whatever the number, without needing to know how long it is.
Because the result is text it will not add up. If the number still has to be used in sums, keep the number and apply a custom format of 00000 instead, which shows the zeros without changing the value.
Questions
Can I use TEXT instead?
TEXT(A2,"00000") does the same on most builds and reads more clearly. Both give text as the answer.
Why did my zeros disappear when I typed them?
Excel read the entry as a number. Format the column as Text before typing, or start the entry with an apostrophe.
How do I get a different width?
Change both the run of zeros and the number at the end to match: eight zeros and 8 for an eight-character code.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
