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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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

:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top