MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing Decimals


Posted by Joe on January 09, 2002 12:48 PM

We hav a bank who wants to recieve our date without the decimals. They say they need to assume that the last two numbers are the cents. So "147.90" needs to read "14790". The problem is that I have to solve this probelm using only the formating features. They cannot handle formulas or macros. I would choose a new bank, but that's not my call. Please help me with this or tell me it is impossible.

Thanks


Posted by Aladin Akyurek on January 09, 2002 12:57 PM

How about using

=ROUND(x,2)*100

where x is a cell or a computation.

Since they assume the numbers always represents N cents, this should work.

Aladin

Posted by Scott on January 09, 2002 1:03 PM


Try highlighting your data, and then selecting Edit-Replace and enter the decimal "." (without the "") in "Find What" and Leave the "Replace With" blank. Hit the button "Replace All".

So "147.90" needs to read "14790". The problem is that I have to solve this probelm using only the formating features. They cannot handle formulas or macros. I would choose a new bank, but that's not my call. Please help me with this or tell me it is impossible.

Posted by Scott on January 09, 2002 1:04 PM

Try highlighting your data, and then selecting Edit-Replace and enter the decimal "." (without the "") in "Find What" and Leave the "Replace With" blank. Hit the button "Replace All".


Posted by Mark W. on January 09, 2002 1:19 PM

Since 147.90 is represented internally as 147.9
your recommendation will produce 1479.00 as
currently formatted or 1479 with a General format.
I can't think of any format that will preserve
the trailing 0 of this integer as desired once
the decimal has been stripped away.

Posted by Scott on January 09, 2002 1:37 PM

Another try.....

Ok, take a blank cell and type 100 in it. Then copy this, highlight your data and do a Paste-Special and select "multiply".