Format for Whole number and 1 Decimal in same Cell

[Sean]

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

 

[Stephen]

I don't think this is possible.

 

[Pete_UK]

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

 

[Bernard Liengme]

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

 

[Ron Rosenfeld]

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