2

For example, if the format is like this: ### ##0,

the number 1283300 will be formatted like this: 1283 300 but I want to display it like this: 1 283 300

or number 75125690347812567 I want to display like this: 75 125 690 347 812 567

I know that I can write the format like this: ### ### ### ### ### ### ##0 etc.

But is there any way to write this succinctly for an arbitrarily long number?

Improve this question
1
  • 2
    No. Apparently . is a special character. Any other character will not be noticed as a thousand separator. You can obviously change this in windows, but I doubt you want a solution that alters everything, not just one worksheet. So for excel that would indeed require ### ### ### ###... ##0
    – LPChip
    Commented May 30, 2017 at 10:39

1 Answer 1

Reset to default
1

For the short numbers (15 or fewer digits) if you want to use space for the thousands separator, you must navigate to Options ► Advanced and enter the space as the thousands separator. You can then format as # ##0 or similar and it will split as you want. This will affect all data within your Excel.

enter image description here

To format the long numbers, you will have to enter it as a text string, since excel's precision limit is 15 digits. If you do that, you will need a VBA routine to format the result (or a complex formula). How, exactly, would you want to handle that?

If you do NOT want to alter Excel's use of your system separators, you will need VBA routines for both.

Improve this answer
5
  • 1
    Do note, this will work for your computer, but someone else who views your worksheet will not see the spaces.
    – LPChip
    Commented May 30, 2017 at 13:39
  • @LPChip Good point. If you want it to appear similar on other computers, you will need to use VBA to either generate the format "on the fly", or to change the Excel separators, perhaps with a Workbook_Open event.
    – Ron Rosenfeld
    Commented May 30, 2017 at 14:06
  • 2
    Using VBA to change this on workbook_open, means that you alter someone's setting indefinitely. Someone might not like to get spaces as separators but has no idea how to restore it back. Making a second Workbook_Close can be as problematic because excel may crash leaving the setting unchanged. The manual ### ### ### ### ### ##0 is still a better option IMHO.
    – LPChip
    Commented May 30, 2017 at 14:26
  • There are other disadvantages, also. It is not something I would recommend, only an option depending on the original posters situation. Personally I would use VBA to format on the fly using a worksheet change event
    – Ron Rosenfeld
    Commented May 30, 2017 at 14:30
  • Another option, depending on the use this as being put to, would be to just change everything into a text string. But we do not have that information
    – Ron Rosenfeld
    Commented May 30, 2017 at 14:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .