fixed decimals

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--

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

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

Vaya con Dios,
Chuck, CABGx3



"GreenThumb" <GreenThumb@discussions.microsoft.com> wrote in message
news:1E338B61-746F-4B24-85F5-D839F916CF97@microsoft.com...
> 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--

Try ..
Enter in an empty cell: 10000, and copy it
Right-click on the target col > Paste special > Divide > OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"GreenThumb" <GreenThumb@discussions.microsoft.com> wrote in message
news:1E338B61-746F-4B24-85F5-D839F916CF97@microsoft.com...
> 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--

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?


"Max" <demechanik@yahoo.com> wrote in message
news:ub7n99IQFHA.3596@TK2MSFTNGP15.phx.gbl...
> Try ..
> Enter in an empty cell: 10000, and copy it
> Right-click on the target col > Paste special > Divide > OK
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "GreenThumb" <GreenThumb@discussions.microsoft.com> wrote in message
> news:1E338B61-746F-4B24-85F5-D839F916CF97@microsoft.com...
>> 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--
>
>

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

On Thu, 14 Apr 2005 12:34:24 -0600, "dave" <davidmartinefski@comcast.net>
wrote:

>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?
>
>
>"Max" <demechanik@yahoo.com> wrote in message
>news:ub7n99IQFHA.3596@TK2MSFTNGP15.phx.gbl...
>> Try ..
>> Enter in an empty cell: 10000, and copy it
>> Right-click on the target col > Paste special > Divide > OK
>> --
>> Rgds
>> Max
>> xl 97
>> ---
>> GMT+8, 1° 22' N 103° 45' E
>> xdemechanik <at>yahoo<dot>com
>> ----
>> "GreenThumb" <GreenThumb@discussions.microsoft.com> wrote in message
>> news:1E338B61-746F-4B24-85F5-D839F916CF97@microsoft.com...
>>> 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--
>>
>>
>

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" <gorddibbATshawDOTca> wrote in message
news:dtmt51ttp60sktvl8e5qv8qvv79ith5g6u@4ax.com...
> 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
>
> On Thu, 14 Apr 2005 12:34:24 -0600, "dave" <davidmartinefski@comcast.net>
> wrote:
>
>>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?
>>
>>
>>"Max" <demechanik@yahoo.com> wrote in message
>>news:ub7n99IQFHA.3596@TK2MSFTNGP15.phx.gbl...
>>> Try ..
>>> Enter in an empty cell: 10000, and copy it
>>> Right-click on the target col > Paste special > Divide > OK
>>> --
>>> Rgds
>>> Max
>>> xl 97
>>> ---
>>> GMT+8, 1° 22' N 103° 45' E
>>> xdemechanik <at>yahoo<dot>com
>>> ----
>>> "GreenThumb" <GreenThumb@discussions.microsoft.com> wrote in message
>>> news:1E338B61-746F-4B24-85F5-D839F916CF97@microsoft.com...
>>>> 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--
>>>
>>>
>>
>

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


On Thu, 14 Apr 2005 16:02:11 -0600, "dave" <davidmartinefski@comcast.net>
wrote:

>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" <gorddibbATshawDOTca> wrote in message
>news:dtmt51ttp60sktvl8e5qv8qvv79ith5g6u@4ax.com...
>> 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
>>
>> On Thu, 14 Apr 2005 12:34:24 -0600, "dave" <davidmartinefski@comcast.net>
>> wrote:
>>
>>>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?
>>>
>>>
>>>"Max" <demechanik@yahoo.com> wrote in message
>>>news:ub7n99IQFHA.3596@TK2MSFTNGP15.phx.gbl...
>>>> Try ..
>>>> Enter in an empty cell: 10000, and copy it
>>>> Right-click on the target col > Paste special > Divide > OK
>>>> --
>>>> Rgds
>>>> Max
>>>> xl 97
>>>> ---
>>>> GMT+8, 1° 22' N 103° 45' E
>>>> xdemechanik <at>yahoo<dot>com
>>>> ----
>>>> "GreenThumb" <GreenThumb@discussions.microsoft.com> wrote in message
>>>> news:1E338B61-746F-4B24-85F5-D839F916CF97@microsoft.com...
>>>>> 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--
>>>>
>>>>
>>>
>>
>

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

On Thu, 14 Apr 2005 16:02:11 -0600, "dave" <davidmartinefski@comcast.net>
wrote:

>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" <gorddibbATshawDOTca> wrote in message
>news:dtmt51ttp60sktvl8e5qv8qvv79ith5g6u@4ax.com...
>> 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
>>
>> On Thu, 14 Apr 2005 12:34:24 -0600, "dave" <davidmartinefski@comcast.net>
>> wrote:
>>
>>>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?
>>>
>>>
>>>"Max" <demechanik@yahoo.com> wrote in message
>>>news:ub7n99IQFHA.3596@TK2MSFTNGP15.phx.gbl...
>>>> Try ..
>>>> Enter in an empty cell: 10000, and copy it
>>>> Right-click on the target col > Paste special > Divide > OK
>>>> --
>>>> Rgds
>>>> Max
>>>> xl 97
>>>> ---
>>>> GMT+8, 1° 22' N 103° 45' E
>>>> xdemechanik <at>yahoo<dot>com
>>>> ----
>>>> "GreenThumb" <GreenThumb@discussions.microsoft.com> wrote in message
>>>> news:1E338B61-746F-4B24-85F5-D839F916CF97@microsoft.com...
>>>>> 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--
>>>>
>>>>
>>>
>>
>