Thousand Separator- How to Change

Barry Katcher

Well-known Member

Joined

Feb 25, 2002

Messages

4,053

• Jun 14, 2003

• #2

I couldn't do it, either. The closest I could come is to use a dash instead of a comma:

#-##-##-###

or

#-##-##-###.00

for two decimal places

Edit: Hold it, I've got it:

# , ## , ## , ### using space,space
or
# ,## ,## ,### using just space,

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Jun 14, 2003

• #3

also - check your regional settings :

start | settings | control panel | regional settings | number / currency tabs

 

Upvote 0

A

arunjhalani

Board Regular

Joined

Oct 8, 2002

Messages

121

• Jun 16, 2003

• #4

Barry : Your Suggestion Worked but the Problem of Extra Spaces is there. Can we avoid that?

Paddy : I ve changed the Settings, but that doesn't work

 

Upvote 0

A

arunjhalani

Board Regular

Joined

Oct 8, 2002

Messages

121

• Jun 16, 2003

• #5

Further I need Dynamic Format i.e. If Figures Changed from 10,000,000 to 100,000 then format should automatically display 1,00,00,000 & 1,00,000

 

Upvote 0

Barry Katcher

Well-known Member

Joined

Feb 25, 2002

Messages

4,053

• Jun 16, 2003

• #6

Sorry about the delay, my email was down for 2 days, just now fixed and I got notification of your post.

I'm afraid I can't help you with either. I had to put the spaces in there in the first place, in order to get the commas in the places you wanted. And I don't know how to avoid the leading comma when you enter 100,000 instead of 10,000,000.

Maybe someone else on the board has an answer.

 

Upvote 0

A

arunjhalani

Board Regular

Joined

Oct 8, 2002

Messages

121

• Jun 17, 2003

• #7

For Dynamic Formatting If I use Underscore _ after the Spaces then I can group the nos with requisite format e.g
10000000 as ##_,##_,##_,##_,###
then I got 1 00 00 000 & if then I Enter 100000, then I got
1 00 000 which is what should expect. Can Anyone improve upon this ?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 17, 2003

• #8

You could try:

[>=10000000]##\,##\,##\,###;[>=100000]##\,##\,###;#,##0

 

Upvote 0

A

arunjhalani

Board Regular

Joined

Oct 8, 2002

Messages

121

• Jun 17, 2003

• #9

Thats Graet Andrew,
But if u can Understand that In India, The group Separator are as First Separator after 1000 & then Subsequent separator after Two Zeros as ##,##,##,###.00.
I hope that I am making Clearer.
This problem has to be tackled by Custom formats or By changing property of Excel so that It follows this format only.

Thanks for your suggestion, it eliminates the Unwanted spaces.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 17, 2003

• #10

To show the decimals use this custom format:

[>=10000000]##\,##\,##\,###.00;[>=100000]##\,##\,###.00;#,##0.00

I got:

1,000.00
10,000.00
1,00,000.00
10,00,000.00
1,00,00,000.00
10,00,00,000.00

Is that right?

 

Upvote 0