Move my Decimals, Please!

budzooka

New Member
Joined
Oct 27, 2004
Messages
2
thanks in advance to anyone who can help me out of this jam....

- I have a column(s) that has 3, 4, and 5 digit numbers that are supposed to represent Dollar figures but the decimal places are not present:

12595 ---> 125.95
695 ----> 6.95
1095 ---> 10.95

- Is there a way to insert a decimal 2 places to the left of each number in the entire column? I only have 18,000+ records.....

- currency formatting is not necessary to include the $ sign

If you can help me out I promise to consult the Beer gods to bless you for eternity

(y)

kindest regards,

budzooka
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Welcome to the forum. Don't panic.

Enter .01 in a blank cell. Then copy it. Finally, select the cells you want to fix and try Paste Special|Multiply. This will multiply all the values by .01 resulting in 2 decimal places. Then format as desired.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
<ul>[*]Type 100 in some unused cell.[*]Copy it to the clipboard.[*]Select the cells that need to be adjusted.[*]From the menu Edit | Paste Special...[*]Click the options for Values and Divide.[/list]HTH
 

budzooka

New Member
Joined
Oct 27, 2004
Messages
2
You are THA MAN!!!!

very friggin' clever, I'll tell ya..... I've been all over the trying to untangle this one. daily Beer Gods consultation for Seti will now go into effect

:pray: (y)


Seti said:
Welcome to the forum. Don't panic.

Enter .01 in a blank cell. Then copy it. Finally, select the cells you want to fix and try Paste Special|Multiply. This will multiply all the values by .01 resulting in 2 decimal places. Then format as desired.
 

shaunac

New Member
Joined
Sep 7, 2005
Messages
2

ADVERTISEMENT

Help!! After using the fix suggested above, all excel...

After using the fix suggested above, ALL my excel files (different files names and everything) automatically insert a decimal point, even though I don't want it there!!!!

In the excel file requiring the fix (let's call it "A"), I entered 0.01 in a cell, copied it and chose "Paste Special" chose the required cells and then chose the "Values" and "Multiply" radio buttons.

Now, all my excel files, whether they are B,C,D etc, unrelated to A.xls at all automatically insert the decimal point even though I don't want it!! :eek: Existing numbers in these files are fine, its only when new files are entered.

ie) want to enter $1250.45, excel changes it to $12.50. Rather than doing the same thing (entering 0.01, copying, and pasting special as "values" and "divide"), is there a way to stop excel from automatically doing this?

Thanks so much for your help!!

Note -- I'm not an idiot with excel, this is not a worksheet within the file, these are separate files that are saved to different folders on my computer...
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Welcome to the forum. Sorry you're getting off to a rocky start.

Couple of questions first.

Under Tools|Options|Edit, do you have Fixed Decimal checked? If so, try unchecking it.

Do you have any macros or worksheet event procedures?
 

shaunac

New Member
Joined
Sep 7, 2005
Messages
2

ADVERTISEMENT

Fixed decimals IS checked. :oops: I unchecked it and things work as they should now. That was too easy... I have no clue how that was changed in the first place. Weird...

Anyways, thanks so much for your help! :biggrin: I'm new to this site and now love it even more!!!!

-Shauna (y)
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
Good catch, Seti. I had forgotten that option even existed; indeed, I don't think I have ever used it.
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Greg,

I use it all the time when I have to key in data with $ and cents to save 1 keystroke per entry. Than I forget about having set it and have to fix a bunch of values in the next sheet that don't have implied decimals. D'oh!
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
Along those lines, I love the TextToSpeech tool for auditing stuff where I have a discrepancy I just can't track down. I can highlight the range and have Excel read the numbers to me while I just read down the column in the report instead of having to flip my eyeballs back and forth between the report and the screen. ('course my co-workers give me a queer look if I don't use my headphones :) ). Ciao.
 

Forum statistics

Threads
1,148,228
Messages
5,745,482
Members
423,953
Latest member
MrC54

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