Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Decimals and Whole Numbers
Bernie,
It works great. Thanks for assistance, Darrell |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numbers & decimals | New Users to Excel | |||
Sorting numbers with two decimals | Excel Worksheet Functions | |||
Changing whole numbers into decimals | Excel Worksheet Functions | |||
Sorting Decimals as Numbers | Excel Discussion (Misc queries) | |||
sort "numbers" with decimals | Excel Discussion (Misc queries) |