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

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" <[email protected]> wrote in message
news:[email protected]...
> 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,
It works great.
Thanks for assistance,
Darrell

Darrell,

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

Bernie
MS Excel MVP


"Darrell Lankford" <[email protected]> wrote in message
news:[email protected]...
> Bernie,
> It works great.
> Thanks for assistance,
> Darrell
>