Excel decimal moving?
 

I am trying to format a cell so that i can input a number and excel will
automatically place the decimal point for me. for eg.

i want to type 12 i want excel to put 0.0012 etc
i want to type 1 i want excel to put 0.0001

can this be done, ive tried to put this in the help but got no joy. pls can
anyone help. I know i can do this by adding other coulmns and doing a
division calculation but that is a messy way for the SS im creating.

thanks
lee

Excel decimal moving?
 

Tools/Options/Edit tag: set fixed decimals to 4

Regards,
Stefi


€žLee€� ezt Ã*rta:

I am trying to format a cell so that i can input a number and excel will
automatically place the decimal point for me. for eg.

i want to type 12 i want excel to put 0.0012 etc
i want to type 1 i want excel to put 0.0001

can this be done, ive tried to put this in the help but got no joy. pls can
anyone help. I know i can do this by adding other coulmns and doing a
division calculation but that is a messy way for the SS im creating.

thanks
lee

Excel decimal moving?
 

From the menu path choose TOOLS / OPTIONS. Click on the EDIT tab. Then
check the FIXED DECIMAL check box. Then enter the number of decimal places
you want in the PLACES field. Click OK and you are good to go. This will
work for every single cell in every single workbook you have. You will have
to turn this off once you are done using it in this manner.

Hope this helps.

Bill Horton

"Lee" wrote:

I am trying to format a cell so that i can input a number and excel will
automatically place the decimal point for me. for eg.

i want to type 12 i want excel to put 0.0012 etc
i want to type 1 i want excel to put 0.0001

can this be done, ive tried to put this in the help but got no joy. pls can
anyone help. I know i can do this by adding other coulmns and doing a
division calculation but that is a messy way for the SS im creating.

thanks
lee

Excel decimal moving?
 

thanks for your prompt info, it helped alot. i was wondering is it possible
to create a macro button to turn this on and off in my toolbar rather than
going into the menu every time i want to input in this way?
thanks

"William Horton" wrote:

From the menu path choose TOOLS / OPTIONS. Click on the EDIT tab. Then
check the FIXED DECIMAL check box. Then enter the number of decimal places
you want in the PLACES field. Click OK and you are good to go. This will
work for every single cell in every single workbook you have. You will have
to turn this off once you are done using it in this manner.

Hope this helps.

Bill Horton

"Lee" wrote:

I am trying to format a cell so that i can input a number and excel will
automatically place the decimal point for me. for eg.

i want to type 12 i want excel to put 0.0012 etc
i want to type 1 i want excel to put 0.0001

can this be done, ive tried to put this in the help but got no joy. pls can
anyone help. I know i can do this by adding other coulmns and doing a
division calculation but that is a messy way for the SS im creating.

thanks
lee

Excel decimal moving?
 

Lee

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

Or try this event code which divides any number entered in Column B by 10000

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Cells.Column = 2 Then
'For a range use
'If Not Application.Intersect(Range("A1:A40"), Target) Is Nothing Then
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value / 10000

End With
End If
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste into that module.

Adjust to suit......Column = 2 is B = 3 is C, etc.


Gord Dibben MS Excel MVP

On Wed, 11 Apr 2007 07:50:02 -0700, Lee wrote:

thanks for your prompt info, it helped alot. i was wondering is it possible
to create a macro button to turn this on and off in my toolbar rather than
going into the menu every time i want to input in this way?
thanks

"William Horton" wrote:

From the menu path choose TOOLS / OPTIONS. Click on the EDIT tab. Then
check the FIXED DECIMAL check box. Then enter the number of decimal places
you want in the PLACES field. Click OK and you are good to go. This will
work for every single cell in every single workbook you have. You will have
to turn this off once you are done using it in this manner.

Hope this helps.

Bill Horton

"Lee" wrote:

I am trying to format a cell so that i can input a number and excel will
automatically place the decimal point for me. for eg.

i want to type 12 i want excel to put 0.0012 etc
i want to type 1 i want excel to put 0.0001

can this be done, ive tried to put this in the help but got no joy. pls can
anyone help. I know i can do this by adding other coulmns and doing a
division calculation but that is a messy way for the SS im creating.

thanks
lee