Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

5 Answers

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
add comment

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
add comment

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
add comment

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
add comment

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 comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.