2

I have this number:

0.0065

If I format it as percentage, I obtain:

0.65%

There is any way to get it with a 'thousand percent' symbol?

6.5 ‰
  • EDIT: I want the cell remain usable as number. Something like =CONCAT(A1*1000, '‰') will be text and not useful for any further operation. – tomelin5 Jan 20 '16 at 16:39
1

I don't think you will be able to create a custom format to change the display. You can use this formula though.

=TEXT(B2*1000,"##0.0‰")

Edit: To allow use of the text value you can use the NumberValue() function. Assuming that the Text() function above is in cell D2:

=NUMBERVALUE(LEFT(D2,LEN(D2)-1))

This will drop the ‰ and treat the remaining number as numeric.

It is a little cumbersome but it works.

| improve this answer | |
  • To return it to its numerical value, don't you need to divide by 1000? – fixer1234 Jan 20 '16 at 20:21
  • Fixer: Yes, you would. My example was to demonstrate the method to get it back to a numeric value. – B540Glenn Jan 20 '16 at 20:29
  • You can use a text format to change the display as long as you are willing to accept that the actual numeric value of the cell will be 1000* greater than the actual ratio. See this answer – Josiah Yoder Jul 11 '17 at 13:56
2

See this post at http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/

Using a combination of the multiply by 1,000 formatting, and ALT+0137 character to add the mille symbol should get you on the right track.

For example, I was able to get ##.0,"‰",%%% to work in Excel 2010.

Remember to wrap text as referred to in the post linked to. Depending on the text in other cells, you might run into the issue of not being able to hide the extra %%% signs though. Others cells referring back to this reformatted cell should be formatted in your normal Number format to display correctly (ie without symbols etc).

| improve this answer | |
1

The universal command for the "Per Mille" symbol () on Windows is ALT+0137.

For OS X, it is SHIFT+OPTION+R.

Source.

| improve this answer | |
  • Yes, I can put the symbol. But I want that the number as 6.5‰ , via formatting, remaining 0.0065 internally. – tomelin5 Jan 20 '16 at 16:18
  • While useful this does not actually answer the original question. – Burgi Jan 21 '16 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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