Fixed Decimals in Excel 2007

M

marojave

Hi,

I have a large spreadsheet with a column of numbers that need to be changed
to currency. I do not need to add zeros, rather I need to add a decimal to
the middle of the number. For example:

Current Number --> Needs to Be
6640 --> 66.40
1428 --> 14.28
1309 --> 13.09

I have tried checking the "fixed decimal" option which adds a decimal to new
entries, but does not change data that is already entered. I have tried
copying and pasting the column and converting the column to text and
importing it into a new spreadsheet, but Excel still recognizes it as old
data and does not add the decimal.

I have also tried various custom display options such as ##.##, 00.00, ect.

Is there any way I can add a decimal to the existing data or will I have to
retype all of the numbers?

Thank you.
 
M

Mike H

Hi,

For these existing numbers here's a workaround. Select an empty cell and enter

'100

Note the apostrophe before the number which prevents excel inserting the
fixed decimal places. Copy this number and select the range of numbers to
convert and then

Right click|Paste special - select 'Divide'

OK

Your done
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
×

מיכ×ל (מיקי) ×בידן

Try this:
* type 100 in some unused cell.
* While that cell is selected - press CTRL+C
* Select the range of numbers that should be "converted".
* Right click one of those cells > choose 'PasteSpecial..." > select
'Divide' > OK.
Micky
 
D

Dave Peterson

Put 100 in an empty cell
Copy that cell
Select the range to fix
Paste special|divide and values
clear the cell with the 100 in it.
 
M

marojave

Thank you so much! I didn't know such an option existed. You saved me a very
long day of ten-keying. I really appreciate it!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top