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

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

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

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

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