How to format number the same way calculators do

[Stefano Gatto]

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.

 

[Mark Lincoln]

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.

 

[Toppers]

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]

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