Converting Numbers from Thousands to Millions

[Bruce212]

I have typed in columns of figures in thousands (e.g, 25,550) that really should have been typed in as millions (e.g., 25,550,000) and need to be displayed that way. Is there any way to convert columns of numbers already typed in as thousands to millions? I don't just want to change formats, I want to change the numbers themselves without having to retype them in. Any advice would be appreciated

 

[ChrisUK]

A quick and dirty way is to:

Add another column, put in the first cell - A1*1000. now copy it down

This will convert all the numbers to millions.

Now, click the new column number, press control c, click the old column and select Edit, Paste Special then select Values from the list

Click the new column header, press delete.

Job done!

HTH

Chris

 

[Smitty]

Welcome to the Board!

You can also put 1000 in an unused cell, copy it, then highlight the range that you want to be in milions and goto Edit-->Paste Special-->Multiply.

Hope that helps,

Smitty

 

[ChrisUK]

Nice one smitty, didn't know you could do that!

 

[KenWright]

A nice trick you can pull with Edit / Paste Special / Multiply is assuming you had table of values, whether they be hardwired or formulas, and you wanted to multiply them all by a value from another cell, but leave it such that you could change that value:-

Assuming data table in A1:H100, in say cell K1 put a number, eg 1, and then in any other cell put =$K$1. Copy the cell you just put that in, select the table A1:H100, do Edit / Paste Special / Multiply and then delete the cell with the formula. You will now have all your cells linked to K1 and can change the value in it at will and see it reflected in changes to all the values in your table.

Another trick is to use it to coerce textual 'numbers' from text format back to numerical format. Put a 1 in a cell, ensure it is numeric, select your text 'numbers' and then paste special / multiply to convert them all.

 

[Bruce212]

Thanks to all. You're a lifesaver (or at least a true timesaver)!