10 Answers
10

You need a function to cut off the decimals. Int() will do that. Mod() will show only the decimal points.

I don't know what behaviour you expect without using functions. Just formatting a number will not change its underlying value. This means that there is no formatting to show only the integer value and disregard the decimals without rounding. Excel does not work that way. Formatting to no decimal points will always include rounding. To work around that, you need a function to cut off the decimals.

If you want the cents to show as whole numbers, just multiply the Mod() result by 100.

Edit: You talk about functions above, but reading other responses, I think what you actually mean is vba routine, a UDF or some other macro. You may want to get your terminology right when asking a question.

You really need to clarify what you want to achieve. It is not clear

where you want the output, e.g. do you want the result in the same cell where the original number is entered? Where should the cents go, then?

do you want the cents to be displayed as 0.55 or as 55?

If you want the values (dollars and cents) to show in the same cell, what should that look like?

if you want the values in two separate cells, please specify which cells for the dollars and which cells for the cents

Just putting a bounty on the question without clearly specifying your requirements does not help much.

Here is another approach, based on the following assumptions:

the value with decimals is entered in column A

the value should be changed in column A to show just the dollars (the integer)

Right-click the sheet tab, click "View Code" and paste the above code into the code window.

Of course, a much, much easier way to achieve exactly the same thing, without functions, without macros, without any VBA, can be done with exactly the same number of keystrokes as entering the number in a cell.

Compare these two sets of keystrokes

24990.55

with

24990Tab55

The second set of keystrokes will put the cents into their own cell, showing them as a whole number.

I'd really appreciate some feedback to the many suggestions that you have received in this thread.

Can you just format the cell as general or text? Those formats won't round.

Alternatively, you could ROUNDDOWN your dollars cell so it always displays the proper dollar figure.

You can also use a formula like

=LEFT(A1,FIND(".",A1,1)-1)

to separate the dollars and cents into separate cells. There are several ways to do this. It doesn't need to be complicated if you're willing to be a little creative.

Whatever route you take, you need to use 3 cells rather than 2. 1 cell for the full amount, 1 cell for dollars, and 1 cell for cents. You must have an input cell and 2 cells with formulas if you're not going to use code to do this.

The number of decimals to be displayed can be set on the Tools menu under options.Select the number of decimals under Fixed decimal options.
Alternately you can use the INT function to extract only the integer portion of the value having decimals.