Displaying numbers in millions into actual figures without multiplying

[Stophson]

Hi,

Someone has sent me a spreadsheet and all the values are in millions. (i.e. 0.1 is in the cell where they mean 100,000). I want to display some of the values as actual figures (i.e. as 100,000) without multiplying the numbers by 1,000,000 (as this will mess up some formulas that are attached to these cells)

Is it possible to change the formatting so that the decimal place is effectively moved to the right rather than the left?

I know you can move the decimal place to the left easily and show a value of 1,000,000 as 0.1 by using a number format like #,##0.0,,;[Red](#,##0.0,,);- but I can't figure out a way to move it the other way

Hopefully you understand what i m trying to do.

Thanks

 

[RonB1111]

This gets you close:
#,##0",".0"00,000"

 

[Stophson]

Thanks for the attempt but that doesn't seem to work.

As a more specific example of what I am trying to do I have the number 0.018399 and I want to show this as 18,399.

 

[RonB1111]

Two other suggestions:

1. Still not perfect: #,###",".000","000

2. Insert a column next to this column and use a formula in the 2nd column multiplying the 1st Col by 1,000,000 and then hide the 1st column.

 

[hrlngrv]

While it's possible to show thousands as factions of millions, it's not possible to show fractions as whole numbers.

You could copy the worksheet ([Alt]+E M to bring up the dialog), which will copy the formatting, then select all cells in the copied worksheet, press [F5] to display the GoTo dialog, click on its Special button to display the Go To Special dialog, and select Formulas returning Numbers. This will likely select many separate ranges. If the active cell were X99 and the original worksheet you copied were named Original Sheet, type the formula

='Original Sheet'!X99*1000000

and press [Ctrl]+[Enter] to enter the 'same' formula (same in R1C1 terms) in all selected cells at once. Repeat this for constant numbers.