Leading Zero's

[HangMan]

Is there a consistant way to format numbers to ensure leading zero's remain intact. I have a series of six digit numbers to which I've applied the 'custom format' 000000 to make sure all the numbers that should have leading zero's appear as six digit numbers, however, if I extract these numbers to a different worksheet, some maintain the formatting, others don't seems to and if I view the format it sometimes automatically changes from 'custom' to a 'special' format for a particular county!

Sometimes a number with leading zero's formatted as 'General' maintains the zero's, other times it doesn't... I can take 001234 formatted as general and copy and paste it and the leading zero's remain intact, (why, I have no idea) but I can equally type the number 001234 also formatted as 'General' at the cell looses the leading zero's!

My numbers may look like this:

123456
003456
012345
123400

and I need to avoid this when copied and pasted etc

123456
3456
12345
123400

I need a guaranteed way of ensuring all my numbers maintain the six digit format and those that have leading zero's maintain them (I've applied the custom format 000000 to all my numbers), is there any surefire way to do this?

Many thanks

[FDibbins]

the only way 001234 will retain the leading zero's after a copy or format to general, is if it is not a number/value in the 1st place. It is probably text that just looks like a number - test it with =isnumber(cell-ref) FALSE will tell you its text

in fact, that may be a way for you to maintain the leading zeros (make them text), and then just add 0 or * 1 to get them back to numbers?

[oeldere]

Maybe you can format the items as text.

You won't be able to count with it, but i don't know if that's a problem.

[Pepe Le Mokko]

Use Copy then Right click -Paste Special and select " Formulas & Number formatting" option -

[VBA FTW]

=TEXT($A$1,"000000") How about a formula?

Set it equal to the cells that have the numbers, then copy the cells with the formula and paste values...play around with the absolute value to suit your needs

[VBA FTW]

Maybe you can format the items as text.

You won't be able to count with it, but i don't know if that's a problem.

What about the COUNTA function?