Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 0 down vote accepted

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.

share|improve this answer

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))
share|improve this answer

What about this

#.00¢ 

for the Cell Format?

share|improve this answer
    
I missed what you were asking. – dbasnett Oct 8 '10 at 15:10
    
It still shows the decimal point. Thanks though – apeterson Oct 8 '10 at 15:11
    
Searching I did not find a solution that was just the format. – dbasnett Oct 8 '10 at 15:31

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

share|improve this answer

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 %
share|improve this answer

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .