How do I enter $1225 as $12.25 without typeing the decmial?

[Guest]

in excel I wat to enter dollars and cent with out typeing in the decmial.
The same way you do in an adding machine

 

[Nick Hodge]

Tools>options...>Edit>fixed decimals and 2

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Nick,
Doesn't that mean ALL entries are of that form? How would you do
it if only one column was required to be as requested?

 

[Nick Hodge]

You couldn't. You could use a helper column

=A2/100

This could then be copied over

You could take a cell with 100 in, copy it and then select your data and
Edit>Paste special....Values and divide

or you could set up some code in the worksheet_change() event, but this may
be overkill...be quite easy to code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Tom Ogilvy]

Original Question:

in excel I wat to enter dollars and cent with out typeing in the decmial.
The same way you do in an adding machine

Toppers interpretation:

if only one column was required to be as requested?

Based on the question asked, sounded like that is what was wanted (fixed
decimal) - there was no mention of a single column.

 

[Gord Dibben]

Toppers

For just one column you would need event code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
'remove the $ sign if don't want currency format
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.


Gord Dibben Excel MVP