How do you get a zero to show up in front of a number if the numbers in the column are all different sizes, such as bank account numbers?


You can use a format string, such as "000 0000 0000" to force a number like 2031234567 to be shown as "020 3123 4567" - the "0" placeholder will force the zero to show, even if it is mathematically insignificant, such as in a bank account number, or phone number, etc.


Adding apostrophe (')

You can simply put an apostrophe (') in front of the number, it will be formatted as text.

For example, input '0012345 will display 0012345.

Format at text

You can also format the cells as text, so that all leading zeros can be displayed without needing to add apostrophe (') every time.


It is recommended to format bank account number as text instead of number. Because Excel stores only 15 significant digits in a number. The 16th digit will be lost for credit card number or if the bank account number is too long.

Reference: I can't write credit card number when I write the last number becomes 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.