Adding leading zeros to numbers which aren't the same length in the same column?

[TheJay]

Hello there, I have thousands of numbers that are missing zeros. I tried custom formatting 000000, but then noticed some of the numbers aren't as long, so it adds two zeros when only one is needed. Can someone please tell me the best way to ensure that whether the number is four, five or six digits long, it only adds one zero to the start? For example, from 6101 to 06101 or 61011 to 061011.

Thank you!

 

[RoryA]

Do you always need to add a 0 at the start? If so, you could use "0"0 as the format

 

[TheJay]

Thank you, that's exactly what I needed. Can you please explain how that works?

 

[RoryA]

The "0" is a literal 0 that is added regardless of the number in the cell. The 0 just reflects a placeholder of at least one number.

 

[TheJay]

That's really well explained, thank you. So it would work on any number that is at least one digit long. I've always wondered about stuff in quotes under custom formatting.

 

[RoryA]

The 0 means that you will always get at least 0 displayed. The fact that there is no decimal point or decimal placeholders means that any decimal places will be rounded - eg 0.4 would show as 0, and 0.5 as 1 - and then the literal 0 will be prepended to that. So 0.4 would show as 00 in the cell.

Anything you put in quotes (or you can use \ for single characters, so \00 would also work, but I find "0"0 clearer ) is literal text and will appear in the cell exactly as typed.

It's worth having a quick read of this: Number format codes

 

[TheJay]

Thank you so much, that's really helpful.