Enter Numbers Starting with Zero in Excel


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

If you’ve ever tried to enter a number starting with zero into an Excel spreadsheet, you will have had the frustrating experience of finding that as soon as you exit the cell, the zero disappears from the front of the number.  Here’s how to stop Excel from ‘helping’ you when it’s not wanted.

enter numbers starting with zero in excel 35247 - Enter Numbers Starting with Zero in Excel

Because zero at the beginning of a number does not change the value of the number, so Excel assumes it’s not needed. If the number is a membership number or product code that needs the zero to stay there.

Never fear, there are a couple of things you can do to tell Excel to leave the number the way you have typed it.

Use an Apostrophe

If you just have an occasional cell where you need to keep the leading zero, all you need to do is type an apostrophe (‘) before the number.

enter numbers starting with zero in excel 35249 - Enter Numbers Starting with Zero in Excel

Note, however that when we exit the cell, even though the number has kept its zeroes, Excel is showing the little green triangle in the corner that flags a problem or something you need to be aware of. Click on the exclamation point next to the cell for more information.

enter numbers starting with zero in excel 35250 - Enter Numbers Starting with Zero in Excel

The message tells us that the cell contains a number stored as text, which is what we want, so we can click Ignore Error.

The apostrophe trick is useful in other situations where Excel gets in the way.  If you want an equals sign = at the start of a text cell, Excel will think it’s a formula.  Type an apostrophe before the = and Excel knows it’s text.

Change the Cell Format to Text

As the note above tells us, what we are doing by putting the apostrophe in front of the number is telling Excel to store it as text, not as a number. If we have a lot of numbers that need to be stored this way, it makes sense to set the format of the cells to text to begin with, so we don’t have to enter an apostrophe every time.

Note that if you want to use this method, you must set the cell format before you enter the numbers. Once the leading zeroes have been removed, changing the cell format won’t put them back.

Let’s say we have a column in our worksheet that will hold membership numbers, and we want to make sure leading zeros don’t get stripped from those numbers. So we want to change the cell format for the whole column to Text before we enter the numbers.

Right-click at the top of the column and select Format Cells.

enter numbers starting with zero in excel 35251 - Enter Numbers Starting with Zero in Excel

Alternatively, you can select the column and click the dropdown under Home | Number and select More Number Formats.

enter numbers starting with zero in excel 35253 - Enter Numbers Starting with Zero in Excel

Then we just select Text on the Number tab and click OK, and we’re done.

enter numbers starting with zero in excel 35254 - Enter Numbers Starting with Zero in Excel

Now any numbers with leading zeros entered in that column will automatically keep their zeroes.

enter numbers starting with zero in excel 35255 - Enter Numbers Starting with Zero in Excel

Use Custom Format to Set the Length of the Number

There’s one more way to make Excel retain leading zeroes, and it’s the only way that can restore zeroes that have been removed. However, it only works if all numbers in the column need to be the same length.

Continuing with our membership number example, let’s say that all our membership numbers are six digits long, and some start with zeroes that Excel keeps removing. In this case, we want to tell Excel that every number in that column should always have six digits. This time in the Format Cells dialog we choose Custom format, then type six zeroes in the Type box.

enter numbers starting with zero in excel 35257 - Enter Numbers Starting with Zero in Excel

This will cause any numbers with less than six digits to have zeroes added to the beginning.

enter numbers starting with zero in excel 35258 - Enter Numbers Starting with Zero in Excel

Excel merge arrays into a single array with VBA

Text formatting in Excel with wrapping, line breaks and merging

Date Formatting considerations in Excel & Office

Justify, Fill, Orientation, Shrink to Fit and other Excel formatting tricks.

subs profile e1563205311409 - Enter Numbers Starting with Zero in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address