Excel question

[The Irish Doggy]

I have a column of numbers and need to add double zeros to the front of every number and have it display correctly. Is there a quick function to add the 00 onto the front of the number?

 

Example:

Turn 2001

Into 002001

[Grits and Shins]

I have a column of numbers and need to add double zeros to the front of every number and have it display correctly. Is there a quick function to add the 00 onto the front of the number?

 

Example:

Turn 2001

Into 002001

 

Format > Cells

 

Click on the Number tab

Select Custom from the Category drop down box

 

enter 000000

[The Irish Doggy]

Format > Cells

 

Click on the Number tab

Select Custom from the Category drop down box

 

enter 000000

 

 

thank you

[Big Country]

Note that that will only work if the number you are formatting is four characters.

 

Another way, assuming your sheet already has the values in it, is to enter 00 in a cell and then use the concatenate formula to combine the "00" cell with you current numbers to add the "00" to the front.

 

Testing on my machine shows this works with the general format on the cells for the numbers and the formulas (but you must format the "00" cell as text to get it to work properly).

Edited May 6, 2008 by Big Country

[westvirginia]

Note that that will only work if the number you are formatting is four characters.

 

Another way, assuming your sheet already has the values in it, is to enter 00 in a cell and then use the concatenate formula to combine the "00" cell with you current numbers to add the "00" to the front.

 

Testing on my machine shows this works with the general format on the cells for the numbers and the formulas (but you must format the "00" cell as text to get it to work properly).

 

This would have been my suggestion...

[Grits and Shins]

Note that that will only work if the number you are formatting is four characters.

 

Another way, assuming your sheet already has the values in it, is to enter 00 in a cell and then use the concatenate formula to combine the "00" cell with you current numbers to add the "00" to the front.

 

Testing on my machine shows this works with the general format on the cells for the numbers and the formulas (but you must format the "00" cell as text to get it to work properly).

 

Only works if the number being formatted is 4 OR LESS characters.

[Big Country]

Only works if the number being formatted is 4 OR LESS characters.

 

Using the custom formatting as you suggested, if you put in a number with 3 characters, it puts 3 leading zeroes, a number with 2 characters gets 4 leading zeroes, and a single character number gets 5 leading zeroes, none of which meet the original criteria of having a double zero leader. Thus I stand by my statement that your method only works if the number in question has 4 characters.

[The Irish Doggy]

For the record, my column had 4.221875671129 digit numbers.

Edited May 6, 2008 by The Irish Doggy

[Big Country]

For the record, my column had 4.221875671129 digit numbers.

 

clearly then the easiest answer is to divide the number by pi before adding the "00"

[Grits and Shins]

clearly then the easiest answer is to divide the number by pi before adding the "00"

 

You couldn't be more wrong. Clearly.

[Big Country]

You couldn't be more wrong. Clearly.

 

divide by 3.14 instead?