Stop Excel from dropping the lead zero

I want to stop Excel from dropping the leading zero, permanently and forever. I know I can format as text and or put in some special number formatting, but the data I work with this quickly becomes a pain. I just want Excel to retain those leading zeros, surely there is a way, an option somewhere that can be changed.

Thanks ahead of time for any help you guys can give me.

Hi DarkArchon,

Not positive if this is what you are looking for but try this:

Right Click the Colum or row the numbers are in
In the dropdown list Click on Format Cells
In the Format Cells Box Click on the Numbers Tab
Scroll down to Custom and in the box under Type: enter the amount of 0's you want to appear in front of your number
Click OK

Hope this helps,

Larry

Not exactly, for 2 reasons. 1) The data I'm entering has varying numbers of zeros leading. 2) I would like a setting for fixing this, permanently and across all documents. Your suggestion, while a little better than setting it as text, still requires me to change every cell/row/column/spreadsheet manually. Thanks though.

Hi Darkarchon,
I think you misunderstood Steve
In fact the number of zeros for the custom format is the total length of the end result
So if your string needs 8 digits, custom format will be "00000000".
When you enter a number it will automatically be formatted with the necessary nr of leading 0 to accomplish a 8 digit long string.
To do this quickly for an entire workbook, select all sheets ( first and last depressing the Shift key -
Ctrl+A
Change the custom format as needed.
You can also use the Macro recorder to record these actions for further workbooks.

HTH

I think I understood him (unless I mis-understand both of you)

What I'm typing in are serial numbers, some may be 4 digits long, ex

0456
2342
0056

some are more

00234112
01231231
34562532

To do it his way, I'd have to format every individual cell for the length of the number required, not all that great of an option. Or change the entire sheet to text, which is what I'm doing now, but I was hoping there was a better way.

Originally Posted by DarkArchon

I want to stop Excel from dropping the leading zero, permanently and forever. I know I can format as text and or put in some special number formatting, but the data I work with this quickly becomes a pain. I just want Excel to retain those leading zeros, surely there is a way, an option somewhere that can be changed.

Thanks ahead of time for any help you guys can give me.

I don't think you are going to find a universally applicable option that turns off Excel's number recognition. If you want to input a number that should be treated as text, then you need to format the cells as text.

You don't give any specifics of why or when this is an issue for you. I would expect that it should only be an issue when a new workbook is created or a new worksheet added to a workbook, or when you execute a command that erases number formats. My best suggestion might be to create a VBA Sub procedure (aka macro) that will perform the offending operation and format as text. For example, the following procedure adds a new worksheet to the end of the active workbook and formats the given block of cells as text.

Please Login or Register  to view this content.

Instead of executing the built in Insert Worksheet command, you would run this macro and have a new sheet ready for inputting numbers as text. You could even assign this procedure to a toolbar button and/or menu item and/or keyboard shortcut.

Originally Posted by DarkArchon

That's just it though, I -DON'T- want it formatted as text, that's what I'm doing now. I want it formatted as number, I just want excel to keep the zero's I type in. So if I opened a bran new excel document and typed in 0234 it would STAY 0234 and not automatically change to 234. I am looking for a universal application option, but not one that turns off Excel's number recognition. I'm looking for (hoping for) one that turns off Excels auto correct so if I type a leading zero it keeps the leading zero.

It's not an "auto correct" thing. It's more of a number thing. When Excel recognizes a number, it automatically stores it as a double precision floating point binary, and ignores any insignificant zeros. To get Excel to show insignificant zeroes requires a number format code.

As you say, one of the difficulties with your data is that they are variable length. How variable are they? Your sample values show either 4 digit or 8 digit numbers. Will all of the values ever used be either 4 or 8 digit or are there other possibilities? Can an 8 digit serial number ever be less than 10,000? If there are only the two possible lengths, and the 8 digit serial numbers will always have 5+significant figures, then I could see a conditional number format possibly working. If I remember the syntax correctly, something like [<10000]0000;00000000. If I remember right, you can have up to three options in such a conditional format, so there's room in a solution like this for 6 or 12 digit serial numbers. See Excel Help for number format codes where this is documented.

If more than three options are needed, or the "break points" aren't clear cut, I'm sure a macro could be coded that would apply the proper number formats to your data. The main difficulty would be understanding the logic behind how to tell how many digits a given serial number is supposed to have.

I don't know if this is an issue in your case, but remember that, stored as numbers, you are limited to 15 or fewer digits in your serial numbers. If you have longer numbers, then they will have to be stored as text. Double precision floating point numbers are limited to 15 digits (in any program that uses double precision to represent numbers, not just Excel).

The default "general" format in EXCEL always tries to guess what type of data you are entering, and not infrequently gets it wrong.

However, as I understand your issue, the only way to get the effect you want is to format the cells as "TEXT".

This would allow any formulas using the values in these cells to treat them either as text or (via the VALUE() function) as numbers.

This might not be exactly what you want, but I think it's the best you can achive with EXCEL.

Mark.