Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
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 "sr25" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
One way:
Format/Cells/Number/Custom 0.\0\00 In article , "sr25" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
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. "sr25" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
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. "Sloth" wrote: 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. "sr25" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
Thanks JE it worked!!!!
"JE McGimpsey" wrote: One way: Format/Cells/Number/Custom 0.\0\00 In article , "sr25" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating the number from 12345.1 to 12345.001
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 "sr25" wrote: Thanks JE it worked!!!! "JE McGimpsey" wrote: One way: Format/Cells/Number/Custom 0.\0\00 In article , "sr25" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
Formating a number | Excel Discussion (Misc queries) |