Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format for Whole number and 1 Decimal in same Cell
How can I format a cell, that if a value is entered as a whole number
it will appear as entered e.g 12, but if a decimal is entered into the same cell it will show to 1 decimal place e.g 12.356, will appear as 12.4. Note both could be typed into same cell Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format for Whole number and 1 Decimal in same Cell
I don't think this is possible.
"Sean" wrote in message ... How can I format a cell, that if a value is entered as a whole number it will appear as entered e.g 12, but if a decimal is entered into the same cell it will show to 1 decimal place e.g 12.356, will appear as 12.4. Note both could be typed into same cell Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format for Whole number and 1 Decimal in same Cell
Here's a way of doing it in a difference cell:
=IF(A1=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0"))*1 Enter the number in A1 and this formula in B1 - format B1 as General. Hope this helps. Pete On Nov 29, 7:16 pm, Sean wrote: How can I format a cell, that if a value is entered as a whole number it will appear as entered e.g 12, but if a decimal is entered into the same cell it will show to 1 decimal place e.g 12.356, will appear as 12.4. Note both could be typed into same cell Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format for Whole number and 1 Decimal in same Cell
If this is just for display purpose and you do not mind using two columns:
Enter you numbers in one column (say A) and use this formula =IF(INT(A1)=A1,TEXT(A1,"#"),TEXT(A1,"#.00")) to display them in another - you may wish to format that cell to align right. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sean" wrote in message ... How can I format a cell, that if a value is entered as a whole number it will appear as entered e.g 12, but if a decimal is entered into the same cell it will show to 1 decimal place e.g 12.356, will appear as 12.4. Note both could be typed into same cell Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format for Whole number and 1 Decimal in same Cell
On Thu, 29 Nov 2007 11:16:26 -0800 (PST), Sean wrote:
How can I format a cell, that if a value is entered as a whole number it will appear as entered e.g 12, but if a decimal is entered into the same cell it will show to 1 decimal place e.g 12.356, will appear as 12.4. Note both could be typed into same cell Thanks I believe you would need to use VBA. You could use an event triggered macro. The below macro assumes that the value is "entered" by typing in a value, and is not the result of a formula. It changes the format depending on whether the entry is an integer or not. Right click the sheet tab and select View Code from the drop down menu. Paste the code below into the window that opens. Be sure to set "c" to the range required. ==================================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Set c = Range("a1:a10") 'Range of cells to be specially formatted If Not Intersect(c, Target) Is Nothing Then With Target If IsNumeric(.Value) Then If .Value = Int(.Value) Then .NumberFormat = "General" Else .NumberFormat = "0.0" End If End If End With End If End Sub ====================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom format whole number to decimal | Excel Worksheet Functions | |||
Number format still uses decimal point | New Users to Excel | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
I want to format a number cell without the decimal and without ro. | Excel Discussion (Misc queries) |