Format option for decimal. e.g., entering 531 results in 5.31

[Wisteria]

Have an option of selecting a "format cell" decimal feature that allows
automatic 2 place value setting. E.g. entering 531 results in 5.31, or $5.31
when you have also selected the cells to be "currency" formated.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...b-d4f901774c45&dg=microsoft.public.excel.misc

 

[Kevin B]

If you click on TOOLS, select OPTIONS and then click the EDIT tab, there's a
checkbox titled FIXED DECIMAL PLACES which does exactly what your suggestion
asks for. It's been around for the last couple of release if I remember
correctly.

 

[David Biddulph]

Have an option of selecting a "format cell" decimal feature that allows
automatic 2 place value setting. E.g. entering 531 results in 5.31, or
$5.31
when you have also selected the cells to be "currency" formated.

You may wish to look at Tools/ Option/ Edit
Fixed decimal places

But remember that this changes the value rather than changing the display
format.

 

[Wisteria]

kevin; Wouldn't it be nice if you didn't have to go through that process to
start that format method, instead of selecting it as an option from the
"format cell" selections in the drop down ribbon? When you change the format
the way you instruct, you also have to remember to change it back when you
are finished.

 

[Wisteria]

David; Wouldn't it be nice if you didn't have to go through that process to
start that format method, instead of selecting it as an option from the
"format cell" selections in the drop down ribbon? When you change the format
the way you instruct, you also have to remember to change it back when you
are finished.

 

[Chip Pearson]

If you want fixed decimal to apply only to a range of cells, you
can use the Change event procedure to change the entered result.
The following code will divide any numeric entry in A1:A10 by
100.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.HasFormula = True Then
Exit Function
End If
On Error GoTo ErrH:
If Not Application.Intersect(Target, Range("A1:A10")) Is
Nothing Then
If IsNumeric(Target.Value) = True Then
Application.EnableEvents = False
Target.Value = Target.Value / 100
End If
End If
ErrH:
Application.EnableEvents = True

End Sub

Right-Click the appropriate sheet tab, choose View Code, and
paste in this code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com