Customizing thousand separator

[kiran]

Hi,

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

123,456,789

But, I wanted the number in the fashion as

12,34,56,789

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

So, please help me......

[Delprat]

hello,

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

select in the first tab the "customized" format type

and in the box enter this format :

00\,00\,00\,000

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...

[kiran]

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 July 12, 2005 by kiran

[Mojo]

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.

[kiran]

Yes, I have tried it for myself. Still, I couldn't get rid of that extra commas. Could any one help me out of this problem.....

[jaclaz]

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

http://www.ozgrid.com/Excel/CustomFormats.htm

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:

=Text(A1;"##,##,##")

and result something like this:

1234  12.34
12345  1.23.45
123456  12.34.56
1234567  1.23.45.67
12345678  12.34.56.78

jaclaz