Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thousand Separator- How to Change

This is a discussion on Thousand Separator- How to Change within the Excel Questions forums, part of the Question Forums category; 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 ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Location
    Kolkatta
    Posts
    121

    Default Thousand Separator- How to Change

    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

  2. #2
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,055

    Default Re: Thousand Separator- How to Change

    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,
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  3. #3
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default Re: Thousand Separator- How to Change

    also - check your regional settings :

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

  4. #4
    Board Regular
    Join Date
    Oct 2002
    Location
    Kolkatta
    Posts
    121

    Default Re: Thousand Separator- How to Change

    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

  5. #5
    Board Regular
    Join Date
    Oct 2002
    Location
    Kolkatta
    Posts
    121

    Default

    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

  6. #6
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,055

    Default Re: Thousand Separator- How to Change

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

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  7. #7
    Board Regular
    Join Date
    Oct 2002
    Location
    Kolkatta
    Posts
    121

    Default

    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 ?

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,463

    Default Re: Thousand Separator- How to Change

    You could try:

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

  9. #9
    Board Regular
    Join Date
    Oct 2002
    Location
    Kolkatta
    Posts
    121

    Default

    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.

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,463

    Default Re: Thousand Separator- How to Change

    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?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com