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
up vote 3 down vote accepted

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;"-"

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.