-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How do I get the Cents Symbol (and others) in Excel?
- Thread starter Fumblina
- Start date
I'm using a different approach to this because, technically, 0.38¢ is not 38 cents, it is $0.0038. And I haven't yet found a way to remove that troublesome decimal symbol and make it appear as simply 38¢
So, I am using the & (aka concatenate) function to combine the 'real value' of 38 cents with a ¢ symbol.
Try this:
Step 1 - In Cell A1 put the ¢ symbol (paste it from Word or use ALT+0162 or 155) as described by others in this post.
Step 2 - In Cell A2 put the number you want to add the cents symbol to. Lets use 0.38. Format the cell as Number with two decimal places.
Step 3 - In Cell A3 enter =A2*100&$A$2
Now the cell in A3 should display 38¢ (without the leading decimal point)!
Caveat: you will not be able to do routine calculations on cell A3.
For example, if you put the following in Cell A4...
=A3*2
...you won't get 0.76 or 76¢, you will get a #VALUE! error because excel can't perform calculations on the text (¢) we added with the &$A$2 trick.
Workaround: I suppose you perform calculations by effectively reversing the concatenation like this.
In cell A4 put...
=LEFT(A3,2)*2&$A$1
...and now you should see 76¢ in cell A4
Note: To get the right number of digits in your cells the way you want them, you may need to format the cells using Format Cells --> Number tab --> Number category.
Good luck!
So, I am using the & (aka concatenate) function to combine the 'real value' of 38 cents with a ¢ symbol.
Try this:
Step 1 - In Cell A1 put the ¢ symbol (paste it from Word or use ALT+0162 or 155) as described by others in this post.
Step 2 - In Cell A2 put the number you want to add the cents symbol to. Lets use 0.38. Format the cell as Number with two decimal places.
Step 3 - In Cell A3 enter =A2*100&$A$2
Now the cell in A3 should display 38¢ (without the leading decimal point)!
Caveat: you will not be able to do routine calculations on cell A3.
For example, if you put the following in Cell A4...
=A3*2
...you won't get 0.76 or 76¢, you will get a #VALUE! error because excel can't perform calculations on the text (¢) we added with the &$A$2 trick.
Workaround: I suppose you perform calculations by effectively reversing the concatenation like this.
In cell A4 put...
=LEFT(A3,2)*2&$A$1
...and now you should see 76¢ in cell A4
Note: To get the right number of digits in your cells the way you want them, you may need to format the cells using Format Cells --> Number tab --> Number category.
Good luck!
Upvote
0
illiniwanderer
New Member
- Joined
- Apr 9, 2015
- Messages
- 3
I've seen other comments on this and other sites that some value in cents is technically 100ths of a cent. Au contraire, cents is 100ths of a dollar.
In the example, 38 cents is 0.38, not 0.0038. The multiplier in the formula is correct.
Rod
In the example, 38 cents is 0.38, not 0.0038. The multiplier in the formula is correct.
Rod
Last edited:
Upvote
0
Similar threads
- Solved
