Changed decimal on fixed numbers

  • Thread starter Thread starter gump
  • Start date Start date
G

gump

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
 
gump said:
The data I'm trying to change is 331864 to 3318.64 or 51500
to 515.00.
Click to expand...

Enter 100 into some cell and copy it. Then select the cells with numbers to
be changed and click Edit > Paste Special > Divide > OK. You might also
need to format the selected cells appropriately, e.g. Number with 2 decimal
places (click Format > Cells > Number).


----- original message -----
 
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.
 
Back
Top