• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel number format - Scaleable space instead of comma

sccowl

sccowl

New Member
Posted this on stackoverflow, trying here for some inspiration.

I want to format some financial data using spaces instead of commas to separate '000's. I am struggling to get the format to scale to larger numbers. Any solution for me needs to by distributed and not use VBA.

Excel scales the format perfectly with commas, using something like #,##0;(#,##0) expanding to add commas every 3 numbers. e.g. 1234567890 converts to 1,234,567,890

However, I want to show this number as 1 234 567 890.

I can force a space into the format, using something like # ##0 which works for up to 6 digits but not more. I could use # ### ##0, but ideally want a scalable solution that can handle any number in the same way regardless of digits.

The above also runs into problems with negatives, as I end up with a space between the brackets on every variation other than the format I use. I can fix -1234567 to (1 234 567) using (# ### ##0) but this becomes ( 123 456) for 6 digits rather than (123 456).

Is there a character code or something else to force a space and make it behave the same way as a comma in the number formatting code.

[Update] Its possible to change on my system by specifying a custom seperator, a space instead of a comma, which works perfectly but only locally on my system.

Thanks
 
This can't be done using Custom Number Format alone.

However, you can use it in conjunction with Conditional formatting.
Ex:
upload_2018-9-11_11-21-30.png

Note that large amount of CF can slowdown workbook performance, CF should be limited to confined range.

I'd recommend going with Hui's suggestion.
 
Hui said:
Actually I would just use (# ### ##0)
Nobody will pickup on the extra space if you have 6 digits
Click to expand...

Thanks guys, really timely replies,

This is my work around and is fine, like you said, no one notices the extra space, its just a shame it doesn't scale as per the comma. I'd prefer not to use CF for the performance reasons you highlight, and I am already using in some areas.

You could argue the space actually makes it even clearer to read, but I'd prefer to add by choice.

I suppose I could scale up myself using (# ### ### ##0) etc but I need to stop somewhere. Its more theoretical beyond this as I don't need multimillions for my use.
 
Haz said:
Try using this format. There's a space after each _
#_ ###_ ###_ ##0;-* #_ ###_ ###_ ##0
Click to expand...

Thanks, this sort of works, but unfortunately I need the brackets for negative rather than an anchored -.

I can live with an extra space inside the brackets as it doesn't look like there is a way to strip out with number formatting.

The _ doesn't function in same way with space as it does other characters - actually that's not true, it does, but it isn't needed. The underscore and following character are ignored and a blank character space of the same size as following character is added, but the space is picked up anyway without the underscore. I used this in my positive numbers with a _) to ensure numbers line up.

I can make the space appear where I need it for most scenarios, the issue is one format doesn't work across all number lengths in the same way it does with the , default separator. However, one extra space inside a bracket is acceptable for numbers under a million.
 
You must log in or register to reply here.
Back
Top