Is there a custom format in excel to show cents values without a decimal

Question

I would like to display 0.37 as 37¢, in Excel. I can do .37¢ with the format .00¢, but 00¢ doesn't work, cause it works with the whole number part.

I know the percentage sign works with the next two digits, like I would like to do, so maybe there is a way to do a percentage formatting, and have a different character in place of the percent sign?

Answer 1

How about multiplying your value by 100, then using your format with a space in place of the decimal point. Of course, you'd have to remember that all of your values are now in cents instead of dollars and fractions of dollars.

Answer 2

You can use a formula with an IF to check for values greater (or less than) 1.

=IF(AND(A1>-1,A1<1),TEXT(A1*100,"00¢"),DOLLAR(A1))

Answer 3

There is a clever workaround to show decimals as whole cents using a number format, and without changing the actual value in the cell. Right click the cell and select Format Cells. On the Number tab, go to Custom.

In the custom number format "Type" field copy/paste the following:

# ¢

Then type this keyboard combination:

Ctrl + J

And finally:

%

Click OK to save the custom number format. Then set the cell format to "Wrap Text".

The % symbol multiplies the number by 100 so your 0.37 shows as 37. It also adds a percent symbol to the format which you don't want.

Ctrl + J hides the percent symbol by inserting a CHR(10) line break that moves the percent symbol to a second line in the cell. Excel won't auto-increase the row height for this cell so it stays hidden.

The end result is that a cell value of 0.37 displays as 37 ¢

Answer 4

What about this

#.00¢ 

for the Cell Format?

Answer 5

I made different options for you. can use any one my brother:

=IF(AND(A1>-1,A1<1),TEXT(A1*100,"0.000¢"),DOLLAR(A1))

=IF(AND(A1>-1,A1<1),TEXT(A1*100,"0.00¢"),DOLLAR(A1))

=IF(AND(A1>-1,A1<1),TEXT(A1*100,"0.0¢"),DOLLAR(A1))

=IF(AND(A1>-1,A1<1),TEXT(A1*100,"0.0000¢"),DOLLAR(A1))

Answer 6

This worked for me: http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/#eg_scale2

e.g. Set the cell alignment text control to wrap text and add this custom number format:

0.#0 ¢ 
[cr]
%

the value 0.0135 is displayed as:

 -------- 
| 1.35 ¢ |  <--visible in cell
 --------
  %         <--wrapped out-of-sight

In order to type the ¢ character type ALT+0162 and for the [cr] character type ctrl-j, so:

0.#0 ALT+0162 ctrl-j %

Answer 7

Add another column and then use this formula. =A1-rounddown(A1,0)

So if you had 106.53 in Cell A1 excel will round the number to 106 and you'd have 106.53-106 and your cell will have the value .53

You can multiply all that by 100 to have a whole number. =(A1-rounddown(A1,0))*100 = 53

And then use format to add the cent symbol