How to format number the same way calculators do
Hello all,
I would like to use the Format function to achieve the following: 1.234567 - 1.23457 1.23456 - 1.23456 1.2345 - 1.2345 1.234 - 1.234 1.23 - 1.23 1.2 - 1.2 1 - 1 (maximum 5 digits after the decimal point) Note that the last result has no dot. I have tried Format(1,"#.#####") but I get "1." and I don't want the dot. Can someone help me to find the correct pattern? Thank you Stefano Gatto, Geneva PS: I also posted this in the VBA forum of msdn. |
How to format number the same way calculators do
Use two Format statements, and choose between the two based on whether
the number is an integer or not. If it's an integer, use Format(yournumber, "0"), else use the format you described. |
How to format number the same way calculators do
Hi,
I don't kow if you can do what you want using FORMAT cell but with code : Sub NF() If ActiveCell - Int(ActiveCell) = 0 Then ActiveCell.NumberFormat = "#####" Else ActiveCell.NumberFormat = "#,####.#####" End If End Sub This code could activated by a worksheet event - is this a viable solution? "Stefano Gatto" wrote: Hello all, I would like to use the Format function to achieve the following: 1.234567 - 1.23457 1.23456 - 1.23456 1.2345 - 1.2345 1.234 - 1.234 1.23 - 1.23 1.2 - 1.2 1 - 1 (maximum 5 digits after the decimal point) Note that the last result has no dot. I have tried Format(1,"#.#####") but I get "1." and I don't want the dot. Can someone help me to find the correct pattern? Thank you Stefano Gatto, Geneva PS: I also posted this in the VBA forum of msdn. |
How to format number the same way calculators do
Many thanks Mark and Toppers. I may not have been clear before, but I wasn't
looking for a cell formatting pattern, but rather for a how to format a number in VBA using the Format() function. Anyway you helped me, since you made me confirm that there is no direct way of obtaining this. Interesting to note however that in Visual Basic 6, this seems to work the way I mean (See http://msdn2.microsoft.com/en-us/library/wb216dct.aspx) Have a nice day (mine is almost over). Stefano Gatto "Toppers" wrote: Hi, I don't kow if you can do what you want using FORMAT cell but with code : Sub NF() If ActiveCell - Int(ActiveCell) = 0 Then ActiveCell.NumberFormat = "#####" Else ActiveCell.NumberFormat = "#,####.#####" End If End Sub This code could activated by a worksheet event - is this a viable solution? "Stefano Gatto" wrote: Hello all, I would like to use the Format function to achieve the following: 1.234567 - 1.23457 1.23456 - 1.23456 1.2345 - 1.2345 1.234 - 1.234 1.23 - 1.23 1.2 - 1.2 1 - 1 (maximum 5 digits after the decimal point) Note that the last result has no dot. I have tried Format(1,"#.#####") but I get "1." and I don't want the dot. Can someone help me to find the correct pattern? Thank you Stefano Gatto, Geneva PS: I also posted this in the VBA forum of msdn. |
All times are GMT +1. The time now is 01:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com