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?


Try this: 0+000 in custom format

enter image description here

| improve this answer | |
  • 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

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.

| improve this answer | |
  • 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

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

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

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.