You can't use cell formatting to physically change the value of an entry
(you can only change how it is displayed). There are two methods you can use
to do what you want.
Method 1
=========
Assuming your first piece of data is in A2, put this formula in an unused
column and copy it down
=A2/100
Then select all the values in that column (the one the formula is in) and
copy them, then click on the first data cell and select Edit/Paste Special
from the menu bar, select Values from the Paste section of the dialog box
and click OK. This will physically overwrite whatever is in the original
data cells with the values displayed by the above formula (which you copied
down). You can then format the cells to 2 decimal places if required.
Method 2
=========
This is a VB macro solution and might prove more useful if you will have to
repeatedly do this process in the future. Press Alt+F11 from the worksheet
to go into the VB editor, click Insert/Module once there and copy/paste the
following into the code window that appeared...
Sub StraightenOutNumbers()
Dim C As Range
For Each C In Selection
C.NumberFormat = "0.00"
C.Value = C.Value / 100
Next
End Sub
Go back to your worksheet, select the cells you want to perform the
conversion on, press Alt+F8, select StraightenOutNumbers from the list and
click the Run button.
--
Rick (MVP - Excel)
"gump" <(E-Mail Removed)> wrote in message
news:B518A301-A0C9-44BF-AC0E-(E-Mail Removed)...
> I've converted a text file into excel and need to change the decimal
> location
> of fixed data. The data I'm trying to change is 331864 to 3318.64 or
> 51500
> to 515.00. No matter what I do I can only get 331864.00 or 51500.00. Can
> anyone help
|
Similar Threads
