Format Decimals and Whole Numbers
 

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

Format Decimals and Whole Numbers
 

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



"Lankchevy" wrote in message
oups.com...
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

Format Decimals and Whole Numbers
 

Bernie,
It works great.
Thanks for assistance,
Darrell

Format Decimals and Whole Numbers
 

Darrell,

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

Bernie
MS Excel MVP


"Darrell Lankford" wrote in message
oups.com...
Bernie,
It works great.
Thanks for assistance,
Darrell