1

I have a spreadsheet containing cent amounts, and I am trying to format the amounts such that the last two zeroes are separated from the rest of the figure by a space, and from then on thousands are separated by spaces again.

The format that I have tried is:

_ * # ### 00_ ;_ * -# ### 00_ ;_ * "-"??_ ;_ @_ 

(there are no decimals in the data because everything is rounded to the nearest cent).

The problem is, Excel automatically converts this format back to

_ * ### #00_ ;_ * -### #00_ ;_ * "-"??_ ;_ @_ 

the moment I press OK.

Is there away to achieve the format I am describing?

  • How about ### ### ### 00 ? – Andi Mohr Oct 10 '13 at 11:05
  • 1
    Can you give some example unformatted numbers and the way you want them to appear? – Andi Mohr Oct 10 '13 at 11:49
  • 1
    It's exactly as i typed it. But perhaps its a localization issue. (Have a dutch version here) Does the # ### 00 also not work? (no ; etc to confuse Excel) – Rik Oct 10 '13 at 14:48
  • 2
    try this instead, perhaps it doesn't like unqualified spaces: ###" "###" "###" "00;-###" "###" "###" "00;"-" – Andi Mohr Oct 10 '13 at 14:51
  • 1
    @Andi, thanks, that does it! I wonder why that would be! – Aaa Oct 10 '13 at 14:52
3

As per discussion in the comments, it appears Adriaan's version of Excel (or possibly Windows settings) doesn't like spaces not contained in quotes. This custom format solves the problem:

###" "###" "###" "00;-###" "###" "###" "00;"-"
| improve this answer | |

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.