Format cells in Excel 1

[kabushnell]

I have a column of cells that I am trying to format in Excel and I can't get it to work quite right. The row contains data like the following, 8010.33. My problem is when the data is 8010.02, Excel contains 8010.2

Does anyone know how I can format this column so that if it is a single digit following the decimal to put a leading zero?

 

[SkipVought]

Hi,

You stated, "My problem is when the data is 8010.02, Excel contains 8010.2"

8010.02 is not equal to 8010.2.

I am confused.

I could understand your wanting TWO places after the decimal so
[tt]
8010.20
[/tt]
But 8010.02 cannot be 8010.2 or vis versa.

???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kabushnell]

You are correct and that is what some of the problem was. However, I was just able to make it work using an IF statement.

Thanks for the quick response though.

 

[SkipVought]

Please enlighten me!

What data did you enter and how did you correct the result?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kabushnell]

The original data was a concatenate formula like the following:

=concatenate($J$1,".",A1) where J1 was 8010 and A1 was 02.

So I put in =if(A1<=9,(concatenate($J$1,".",0,A1)),(concatenate($J$1,".",A1)))

 

[SkipVought]

A1 was 02

Your problem was that the number 02 is equal to 2.

You might have 02 entered in a cell, but excel handles it as 2.

Even if the value was stored as TEXT '02, when Excel puts it together in the formula to return the result, it CONVERTS '02 to 2.

I understand your solution. Since you are dealing with numbers and not text (which CONCATENATE uses), here's a better ARITHMETIC solution...
[tt]
=J1+A1/100
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kabushnell]

Thanks, that formula does work the same and is a little easier to use. I was going to put in the original post that the cell contained a formula but, at first, I thought it was just a formatting issue. Then I did realize that even though cell A1 displayed 02, it was still only stored as 2.

Thanks for your help.