Copy/Paste Special Excel 2

[Kallen]

Hi all,

I have a spreadsheet with a bunch of numbers. What I would like to do is put in some kind of formula that will divide each number by 1000. The end result would be my dollar amount of $1,000,000 would be $1,000. Which is the format I need for all the numbers in my report.

I see that there is a divide function in "Paste Special" , but whenever I try and use it it doesn't work. Does anyone know the easiest solution to this dilema?

Thanks

TGIF )

 

[pbrodsky]

You can format the number to show thousands (a custom format ending with a comma e.g. "0,". This would not change the value but only the way the numbers are displayed.
Otherwise you can us VBA. eg.

Sub To_K()
For Each c In Selection
v1 = c.Formula
If IsNumeric(v1) Then
c.Formula = v1 / 1000
End If
Next c
End Sub

 

[KenWright]

The divide function does work, but you are probably not applying it correctly. Put 1000 in an empty cell, copy it, select the data in question and then do Edit / Paste Special / Divide.

That having been said - Do you really want your data to physically change to be 1000 times smaller, or would a format be good enough.

If you select your data and do Format / Cells / Custom you will probably see the default of:-

#,##0

If you change that by adding a , at the end so it becomes:-

#,##0,

you will find that all your data appears to be 1000 times smaller - Though the real data is still there. The comma simply suppresses the last 3 digits.

If you want to stipulate it is in K (Thousands), you could also change it again to be:-

#,##0,"K"

Which will suppress the last 3 digits and put a K on the end.

You can also add two commas and turn your data into millions.

Regards
Ken..................

 

[costfreak]

Another option would be to enter the number 1,000 into each of the cells in which you paste the data. When you do copy, paste special, divide, it will divide the number you're pasting by the number in the cell you're pasting TO. THAT's how that paste special works.

Formatting is a good option and if you know VBA than use the macro posted above.

 

[Kallen]

Thanks for all the replies!! I did the custom format and it worked like a charm. You saved me a bunch of work!!

 

[KenWright]

Glad we could get you sorted.

Regards
Ken...............