custom number format

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi,

Is there a way (besides formatting as text), to have numbers show as in the
following example? i.e., :

25
40.01
40.10
etc

I can't seem to get the decimal point to disappear when I enter a number
without one.

TIA

Jim
 
Hi Jim,

You could use a User-Defined Function to apply the formatting. The following does that for the range A1:A100:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
If Intersect(Target, .Range("A1:A100")) Is Nothing Then End
With Target
If Int(.Value) = .Value Then .NumberFormat = 0
If Int(.Value) <> .Value Then .NumberFormat = "0.00"
End With
End With
End Sub

To use this, open the visual basic editor (Alt-F11), then double-click on the relevant worksheet module for your workbook (eg 'Sheet
1') and paste the code into it. Then press Alt-F11 again to return to the workbook.
 
Thank you; I haven't worked with UDF's before, but I might as well take the
plunge now!

Jim

macropod said:
Hi Jim,

You could use a User-Defined Function to apply the formatting. The
following does that for the range A1:A100:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
If Intersect(Target, .Range("A1:A100")) Is Nothing Then End
With Target
If Int(.Value) = .Value Then .NumberFormat = 0
If Int(.Value) <> .Value Then .NumberFormat = "0.00"
End With
End With
End Sub

To use this, open the visual basic editor (Alt-F11), then double-click on
the relevant worksheet module for your workbook (eg 'Sheet 1') and paste
the code into it. Then press Alt-F11 again to return to the workbook.

--
Cheers
macropod
[Microsoft MVP - Word]


Jim said:
Hi,

Is there a way (besides formatting as text), to have numbers show as in
the following example? i.e., :

25
40.01
40.10
etc

I can't seem to get the decimal point to disappear when I enter a number
without one.

TIA

Jim
Click to expand...
Click to expand...
 
Hi,

Is there a way (besides formatting as text), to have numbers show as in the
following example? i.e., :

25
40.01
40.10
etc

I can't seem to get the decimal point to disappear when I enter a number
without one.

TIA

Jim
Click to expand...

If you "can't get the decimal point to disappear", most likely you have
selected the fixed decimal option.

Where to find this depends on your Excel version

For Excel 2007+ -- Office Button
Excel Options (down at the bottom of the window)
Advanced
DeSelect "Automatically insert a decimal point"

For earlier versions, it is on one of the tabs that shows up when you select
Tools/Options from the main menu bar.

Then format your cell(s) as "General"
--ron
 
Back
Top