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?

Improve this question
14
  • How about ### ### ### 00 ?
    – Andi Mohr
    Oct 10, 2013 at 11:05
  • 1
    Can you give some example unformatted numbers and the way you want them to appear?
    – Andi Mohr
    Oct 10, 2013 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, 2013 at 14:48
  • 2
    try this instead, perhaps it doesn't like unqualified spaces: ###" "###" "###" "00;-###" "###" "###" "00;"-"
    – Andi Mohr
    Oct 10, 2013 at 14:51
  • 1
    @Andi, thanks, that does it! I wonder why that would be!
    – Aaa
    Oct 10, 2013 at 14:52

1 Answer 1

Reset to default
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
0

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.