Excel – Make a rule to force a three numbers display, “null” replaced by 0

Posted on

QUESTION :

I want to make a rule into a numeric column in Excel.
I have like 800 cells in that column. Each are numeroted from 1 to 800.

My goal is to have a rule that make this change :

from –1 / –2 / -12 / 800
to 001 / 002 / 012 / 800
(and not 0012 or 00800 !)

In other words, force a 3 numbers numerotation with 0 instead of blank.

Please pardon my poor english,

Thank you for your help.

enter image description here

ANSWER :

Another way to do this (other than use Excel’s number format option) is to use the text formula

=TEXT(A1,'000')

Microsoft’s Help Link for this Formula

enter image description here

You will want to use a custom number format.

First select the 800 Cells this should apply to.

Right click and select Format Cells.

The see image below, select Custom from the left hand side, and type 000 in place of general.

enter image description here

For more info see: https://support.office.com/en-gb/article/create-and-apply-a-custom-number-format-6c308025-35da-4047-9481-c146a12063df

Leave a Reply

Your email address will not be published. Required fields are marked *