Jump to content

Customizing thousand separator


Recommended Posts


I am working in Office 2000. I am from India. I am facing a problem that I could not change the location of the thousand separator.

For Example :

The number : 123456789

I wanted to enter this in a numeric data type which shows as


But, I wanted the number in the fashion as


And I could not get it though I tried in regional settings, custom data type in excel.

So, please help me......

Link to comment
Share on other sites


select the cell(s), hit Format > Cell menu

select in the first tab the "customized" format type

and in the box enter this format :


the zeros are placeholders for numbers

the \, displays comas at the right place without giving them a "numerical" meaning (antislash is the escape char.)

thus the cell content will remain numerical, but the display will be good

i'm not sure i'm clear, you may see the help...

Link to comment
Share on other sites

I tried this.

But Hey,

Now I have one more problem.

This is working for the number having 9 digits. What if only 4 or 5 or <9 digits.

See. I typed the number 12345 and it looks as 00,00,12,345. Whereas I don't want the 0's or commas before 12345. Right.

Please let me know the solution......

Edited by kiran
Link to comment
Share on other sites

  • 1 month later...

If you change the format from 00\,00\,00\,000 to ##\,##\,##\,### you will loose the extra zeroes, but the commas will still be displayed.

I don't know how to get around that myself.

Link to comment
Share on other sites

You could try adding some "conditional" format, see here:


i.e. a format for numbers less than 10000 and another one for numbers bigger than 999999, but it seems like you have just two conditions to be put together.

Or you could (provided that it can be done on the particular sheet you are working) input numbers with general formatting in a column, hide it and make a text column with a link to them, formula would be something like:


and result something like this:

1234  12.34
12345  1.23.45
123456  12.34.56


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...