0

I would like to create a custom format number in Excel:

For example:

When I write: 0, I would like to display: 0+000 When I write: 55, I would like to display: 0+055 When I write: 555, I would like to display: 0+555 When I write: 5555, I would like to display: 5+555

How can I do this?

1

Try this: 0+000 in custom format

enter image description here

  • As TEXT(value,format) works very similar to the custom formatting, this is equal to =TEXT(A1,"0+000") for a value in A1 – Hannu Oct 30 '14 at 19:50
1

I don't think this is possible with custom formats, you need a formula:

If you have the value to "format" in A1:

=TEXT(INT(A1/1000),"0+") & TEXT(MOD(A1,1000),"000")

... which will work for up to four-digit numbers.

  • custom format possible. Tried and Tested. – ZAT Oct 30 '14 at 18:49
  • Well, =) that was unexpectedly easy. That might be an undocumented feature - meaning: it will break at some time. – Hannu Oct 30 '14 at 18:52
0

If you know how to get to cell format select custom format. Theres a text box to the right and simply put the prefix in quotes followed by the number format. Ie "0+"0

  • 2
    Is not working for 5+555 – Peter Parker Oct 30 '14 at 18:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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