Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changed decimal on fixed numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changed decimal on fixed numbers
Divide by 100.
If this post helps click Yes --------------- Jacob Skaria "gump" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changed decimal on fixed numbers
"gump" wrote:
The data I'm trying to change is 331864 to 3318.64 or 51500 to 515.00. 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 ----- "gump" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changed decimal on fixed numbers
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with fixed decimal | Excel Discussion (Misc queries) | |||
Fixed Decimal | Excel Worksheet Functions | |||
Format Numbers Without Fixed Decimal | New Users to Excel | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) | |||
Fixed decimal place | Setting up and Configuration of Excel |