Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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! –  Adriaan Joubert Oct 10 '13 at 14:52

1 Answer 1

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;"-"
share|improve this answer
1  
Thanks Andi and Rik for all the help. –  Adriaan Joubert Oct 10 '13 at 14:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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