currency format

[Lee nameloc]

Is there a way to format cells so that when a number is
entered as "125" Excel automatically formats it to 1.25 or
$1.25? I have tried the special formats, number and currency
formats with no luck. I am sure it can be done and is
something that is staring me in the face.
Thanks,
Lee

 

[Frank Kabel]

Hi
one way:
- goto 'tools - Options - View' and check 'Fixed decimals'
Note: this will change all your values and affect the entire workbook.

 

[Guest]

On the Tools menu, click Options, and then click the Edit tab.
Select the Fixed decimal check box.
In the Places box, enter a positive number of digits to the right of the decimal point or a negative number for digits to the left of the decimal point.
For example, if you enter 3 in the Places box and then type 2834 in the cell, the value will be 2.834. If you enter -3 in the Places box and then type 283, the value will be 283000

Data you entered before selecting the Fixed decimal option is not affected

 

[Gord Dibben]

Lee

If you want the number to actually be 1.25 go to Tools>Options>Edit and check
"Fixed decimals" at 2

Format as Currency.

Note, this will affect all newly-entered numbers, not the existing ones.

To change existing numbers, enter 100 in a cell and select the cells with
numbers and Paste Special>Divide>OK>Esc.

If it is just one column or row of numbers to be entered, you could use an
event change code to invoke the "fixed decimals" for just that column or row.

Alternate......to toggle "fixed decimals" on/off

Sub fixed_decimal()
Application.FixedDecimal = Not Application.FixedDecimal
End Sub

Gord Dibben Excel MVP

 

[Lee Coleman]

Thanks to all! I knew it was something very simple but just couldn't see it.
Thanks again,
Lee Coleman