Format Decimals and Whole Numbers

[Lankchevy]

Is it anyway to setup a cell (Format/Cells/Number/Custom) so when the
number is a whole number, it shows as a whole number and when a number
has a decimal, it shows as a decimal? For example our system has an
eight digit number as the following:

12311001
12412002

They show as:

123-11-001
124-12-002

When we add a mod 1, 2 etc. to the existing number we want it to show
the following:

123-11-001
123-11-001.01
123-11-001.02
124-12-002
124-12-002.01

I'm trying to keep it from adding the 2 zeros to the end of the
original number like the following:

123-11-001.00

Any help would be greatly appreciated.
Darrell

 

[Bernie Deitrick]

Darrell,

Copy the code below, right click on the sheet tab, select "View Code" and paste the code into the
window that appears.

Change the B:B to be the column (or columns) where you want to enter the 8 digit numbers.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If Target.Value <> Int(Target.Value) Then
Target.NumberFormat = "000-00-000.00"
Else
Target.NumberFormat = "000-00-000"
End If
End Sub

 

[Darrell Lankford]

Bernie,
It works great.
Thanks for assistance,
Darrell

 

[Bernie Deitrick]

Darrell,

Glad to be of help, and thanks for letting me know that you got it to work.

Bernie
MS Excel MVP