dividing values by 1,000 in a cell

Dave1969

New Member
Joined
Jan 8, 2009
Messages
32
Is there a simple way to divide values in a cell by 1,000?

Let's say I have thousands of cells with numbers formatted as $...for example, $9,437. However, I want these all to display as $9.4 and then I'll put a $k in the header. The $9,437 number is hard coded. How can I do this to thousands of numbers quickly? The only way I know is to use a new column, do a formula there, then copy and paste as values. Is there a faster/better way to do it right inside the existing cell?

Thanks,

Dave
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you actually want to change the values (rather than just the display).......

Put 1000 in an empty cell then copy that cell.

Select range of values to convert

Edit > Paste Special > Divide > OK
 

Dave1969

New Member
Joined
Jan 8, 2009
Messages
32
If you actually want to change the values (rather than just the display).......

Put 1000 in an empty cell then copy that cell.

Select range of values to convert

Edit > Paste Special > Divide > OK
That's it...perfect! Yes, I wanted to change permanently. I always give a small joke when someone helps me...so here you go.

Did you hear that Fed Ex and UPS are merging? The new company will be called ...

Fed Up

:)
 

yytsunamiyy

Well-known Member
Joined
Mar 17, 2008
Messages
963
alternatively you can use the following macro:

Code:
Sub test()
Dim Total_Rows As Double
Dim Counter As Double
Total_Rows = Cells(Rows.Count, "A").End(xlUp).Row
For Counter = 2 To Total_Rows
    Cells(Counter, "A").Value = Cells(Counter, "A").Value / 1000
Next Counter
End Sub

The Macro works on the following assumptions:
Your data to be divided is in Column A - to adjust change all instances of "A" to your actual column
Your data to be divided starts in the second row, Row 1 being the header row. To adjust cange the 2 in line
Code:
For Counter = 2 To Total_Rows
to your starting-row number.
 

Dave1969

New Member
Joined
Jan 8, 2009
Messages
32
thanks to all posters...the first solution worked best for me, appreciate the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top