Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel nuumber format with 10 digits 2
- Thread starter skygirl65
- Start date
-
1
- #2
earthandfire
Programmer
select your range then ....
Using the Number Tab on the Format | Cells... menu,
Select Custom from the list on the left and in the Type textbox enter ten 0 i.e. 0000000000 click OK
Hope this helps.
Using the Number Tab on the Format | Cells... menu,
Select Custom from the list on the left and in the Type textbox enter ten 0 i.e. 0000000000 click OK
Hope this helps.
-
1
- #3
AnotherHiggins
Technical User
skygirl65,
What do you want to do with these numbers?
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.
What do you want to do with these numbers?
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.
earthandfire
Programmer
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.
Hope this helps.
Hope this helps.
AnotherHiggins
Technical User
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.
[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]
Help us help you. Please read FAQ181-2886 before posting.
earthandfire
Programmer
I agree, it was just my understanding of the original question. Your suggestion, as you say converts the numbers to a fixed width string.
AnotherHiggins
Technical User
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.
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.
- Status
Similar threads
