BeginnerText Functions
Pad order numbers to five digits with TEXT
Show a number with leading zeros so it matches the format another system expects.
TEXT
4 min10 points
TEXT
Formats a number as text using a format code, for padding, percentages and fixed decimals.
Syntax
TEXT(value, format_text)
Text Functions.
Practise TEXT1 interactive exercise, free, no account needed
A worked example
=TEXT(42,"00000")
Returns 00042
Returns the number as text shaped by the format code: five digit positions, padded with zeros from the left.
Where it goes wrong
The result is text. It will not sum, will not sort as a number and will not match a numeric lookup value, so pad for display or export and keep the real number in its own column.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- Three zeros is right for a two-digit number and wrong for every other length: order 1183 would become 0001183. The amount of padding has to depend on the number, which is what a format code does.Pad order numbers to five digits with TEXT
- A number cannot hold leading zeros — Excel stores 42 as 42 however you format it, and the export reads the stored value. The cell has to become text to keep the zeros.Pad order numbers to five digits with TEXT
Practise TEXT
Knowing what a function does and being able to write it from memory are different skills. These close the gap.