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 strongm 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

Status
Not open for further replies.

skygirl65

Technical User
Jul 18, 2004
32
US
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?
 
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.
 
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.
 
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.
 
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.
 
I agree, it was just my understanding of the original question. Your suggestion, as you say converts the numbers to a fixed width string.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top