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

What about this

#.00¢ 

for the Cell Format?

Answer 4

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))