formating the number from 12345.1 to 12345.001

[sr25]

I have the column with the data 1234.1 or 12345678.1 and I would like to
change them adding 00 zeros right after the period and the result should be
1234.001 Any help is appreciated.

 

[Ron Coderre]

That's not a formatting issue....you want to actually change the value of the
cell.

This formula might work for you:
A1: 1234.1
B1: =INT(A1)+MOD(A1,1)*0.01

Does that help?

***********
Regards,
Ron

 

[JE McGimpsey]

One way:

Format/Cells/Number/Custom 0.\0\00

 

[Sloth]

Formatting will only change how it looks, but you could do it using
#."00"###
as the custom number format. If you actually want to change the cell you
will need a dummy column using text functions to change the number and then
copy and paste special pasting values.

 

[Sloth]

It took me a while to figure out the formula you need, and then I saw Ron's
post, but this formula will also work.
=VALUE(TEXT(A1,"#.\0\0#"))
This will work if you have only one digit after the decimal.

 

[CLR]

I would use Data > TextToColumns, using the decimal as the delimiter and in
C1 put this formula and copy down, (note, column C must be formatted for
three place decimals...........

=(A1&".00"&B1)*1

Vaya con Dios,
Chuck, CABGx3

 

[sr25]

Thanks JE it worked!!!!

One way:

Format/Cells/Number/Custom 0.\0\00

 

[CLR]

Be advised, a formatting solution is only an "appearance"
solution.............the numbers will still be 12345.1 and 12345678.1 if you
have to do any math with them............

Vaya con Dios,
Chuck, CABGx3