I need excel to change the format of a series of number to always be 10 digits. Some are 8 some are 9, but I need to add 0, or 00 at the beginning so that all numbers will be 10 digits. Any Ideas?
I ask because earthandfire's solution will keep these stored as number, which is great for some purposes. But you must understand that while the numbers display with 10 digits, they are all still stored as numbers.
If you want to force there to really be leading zeros, then you must convert these numbers to text. This could be accomplished by using a formula. Say your first value is in A1. Then you can use [COLOR=blue white]=text(A1,"0000000000")[/color] to convert all numbers to text strings with leading zeros.
To see the difference between these two methods, you can use the LEN function or Copy 'n' Paste Special > Values.
[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]
Help us help you. Please read FAQ181-2886 before posting.
anotherhiggins, I based my suggestion on skygirl65's request to format of a series of number to always be 10 digits, and so assumed that resultant numbers would be required.
Right, but they aren't actually 10 digits - they only display as 10 digits. 123 will display as 0000000123, but =len(a1) will reveal the it is still only 3 digits long.
[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]
Help us help you. Please read FAQ181-2886 before posting.
Agreed. And apparently your suggestion is what the OP was looking for which means that your reading of the question was better than mine. I just wanted to make sure that skygirl65 understood her options.
Nice job, earthandfire.
[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]
Help us help you. Please read FAQ181-2886 before posting.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.