Thousand Separator- How to Change

arunjhalani

Board Regular
Joined
Oct 8, 2002
Messages
121
I need to change the Thousand separator grouping.

E.g 10,000,000 is to displayed as 1,00,00,000. This is because I have this system followed in our area. I am not able to change this in Custom format Properties.
Thanks in advance

Arun J
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
also - check your regional settings :

start | settings | control panel | regional settings | number / currency tabs
 
Upvote 0
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
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
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
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
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
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

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top