Adding a decimal

[Rick]

I have 3000 lines on an Excel spreadsheet that have a cost
column (this is extracted data using ODBC). The cost
comes over with no decimal, but is supposed to have a
decimal 3 spaces in.....sample, cost should be $2.50, but
comes over at $2500. The format cell function only seems
to add numbers after the existing number....making it
$2500.000. How can I insert a decimal in the proper place
for this column so I don't have to do it manually for 3000
lines?

 

[Akshay Bakhai]

Create/use another column adjacent to this column.

Let us assume that your costs are in range C2 through
C3001. Then in cell D2 key in the formula = C2/1000.
Copy cell D2; and then highlight cells C3 through C3001
and paste the formula.


If you do not like to have an additional column, then
perform the following additional steps after having done
the above steps.

Select range D2 through D3001 and copy it. Go to cell C2
and PASTE SPECIAL - VALUES. Thereafter delete column D.


Please change references to C2, C3001, D2, etc. in the
above example based on your real situation.

 

[Bernard V Liengme]

While Akshay's methods will work, this is quicker:
In a blank cell (say Z1) enter the value 1000 and click the Copy tool
Select your range of value and use Edit|PasteSpecial-check the Divide box
and click OK
Number are now as required, delete Z1
Best wishes
Bernard

 

[Myrna Larson]

Assuming the decimal point belongs between the 2nd and 3rd characters *counting from the right*
(i.e. the values are in pennies and you want dollars):

Put the number 100 in a cell. Edit/Copy that cell. Then select the cost column and Edit/Paste
Special, and select the Divide and Values options.