fixed decimals

[GreenThumb]

Is there a formula I can use to fix decimals on values already on a
spreadsheet? I am importing data from a database that assumes 4 decimal
places, so a 70000 is really 7.0000. I tried using the fixed formula but
that did not give me the desired result. I also went to Tools -> Options ->
Edit -> Fixed Decimal Places and set to 4 but that only helps for newly
entered data. Please help!

Thank You!

--GREG--

 

[CLR]

Select the cells you wish to format........

Right-click > FormatCells > NumberTab > Number > set for 4 Decimal
places..........

Vaya con Dios,
Chuck, CABGx3

 

[Max]

Try ..
Enter in an empty cell: 10000, and copy it
Right-click on the target col > Paste special > Divide > OK

 

[dave]

I have a similar question although its in Quattro pro. I would like to be
able to enter numbers without have to type in the decimal point. Example
1234 automaticly appears 12.34 idealy $12.34.

Anyone here know how I could achieve this?

 

[Gord Dibben]

dave

Only on a global basis.

Tools>Options>Edit. Checkmark Fixed Decimal Places and set to two.

Individual cells must be done manually or through event code.


Gord Dibben Excel MVP

 

[dave]

Thanks for replying, would you happen to know the steps to do this manualy,
I have only been able to figure out how to do it to the whole page not just
a few select cells.

 

[Gord Dibben]

dave

Manually.........

enter 12.34 and format as currency.

Worksheet event code........

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Select your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.

If your range is not contiguous you can change the Me.Range like this

("A5,C1,C5,F1,F8,I6,H3,H13,D13,C15,F18,F13")


Gord Dibben Excel MVP

 

[Gord Dibben]

dave

A third method, also manual.

Select the cells to format using SHIFT + F8 to get into ADD mode then click
your way around.

Insert>Name>Define and name this range.

F8 twice to get out of ADD mode.

Enter numbers as 1234.

When you want to change just those few, enter 100 in an empty cell and copy.

From name box select your named range and Paste Special>Divide>OK>Esc


Gord