+ Reply to Thread
Results 1 to 4 of 4

Change Dots into Commas as Decimal Divider

  1. #1
    xenia
    Guest

    Change Dots into Commas as Decimal Divider

    Hello

    I wonder if anybody can help?

    I create a lot of automated reports. These reports include costing and
    percentages, with several decimal places. In the English format the "1000
    Seperator" is a comma, and the decimal places are seperated with a dot.

    Now the problem:
    I have to send the reports to clients in mainland Europe, where the "1000
    Seperator" is a dot, and the decimal places are seperated with a comma.
    So far I could only remove the "1000 Seperator" entirely (in Format cells),
    send it to the Europeans, and they had to change the Comma on the decimal
    palces manually. Sometimes this is very difficult, since Excel in Europe
    treats that dot like a "1000 Seperator", and removes the 0 in front of the
    dot.

    I cannot ask them to change their country setting, since they need to use
    their country specific settings for everything they are doing. ALso I cannot
    change my settings, since I work with English/America clients also.

    Is there any way to automatically transfer the commas into dots, and vise
    versa?

    Thanx

  2. #2
    Marty
    Guest

    RE: Change Dots into Commas as Decimal Divider

    You said: “I cannot ask them to change their country setting..." I'm not
    sure this is what you're talking about or not so I apologize in advance if
    you are. When I went in to Control Panel > Regional and Language Options >
    Customize I changed the “.” to a "," for decimal numbers. Then I changed ","
    for a "." for 1000 separator. Then I opened Excel, typed in a number and the
    number looked like this: 120.000.000,00; just what I expected. I saved the
    WS; changed the settings back to the way the were; then I reopened the WS and
    the number appeared like this: 120,000,000.00. It would seem to me that if
    everyone had their Regional Settings in Control Panel set to their specific
    country's format nobody would have to change anything. Have you explored
    this option?

    Marty

    "xenia" wrote:

    > Hello
    >
    > I wonder if anybody can help?
    >
    > I create a lot of automated reports. These reports include costing and
    > percentages, with several decimal places. In the English format the "1000
    > Seperator" is a comma, and the decimal places are seperated with a dot.
    >
    > Now the problem:
    > I have to send the reports to clients in mainland Europe, where the "1000
    > Seperator" is a dot, and the decimal places are seperated with a comma.
    > So far I could only remove the "1000 Seperator" entirely (in Format cells),
    > send it to the Europeans, and they had to change the Comma on the decimal
    > palces manually. Sometimes this is very difficult, since Excel in Europe
    > treats that dot like a "1000 Seperator", and removes the 0 in front of the
    > dot.
    >
    > I cannot ask them to change their country setting, since they need to use
    > their country specific settings for everything they are doing. ALso I cannot
    > change my settings, since I work with English/America clients also.
    >
    > Is there any way to automatically transfer the commas into dots, and vise
    > versa?
    >
    > Thanx


  3. #3
    Registered User
    Join Date
    05-06-2005
    Posts
    1
    I've just had a similar issue. I have data for English and French on the same sheet purely for display purposes (no further calculation needed from that point onwards). For the French lines I use the following function for percentage values:

    =substitute(text(A1,"0.0%"),".",",").

    This can be expanded to switch the thousand separator as well:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1,"0,000.00"),",","x"),".",","),"x",".").

    The x is in there not to get them mixed together in the conversion process.

    Just remember that the result will be text, not numbers.

    I'm stuck however trying to do the same thing for labels in charts ... you simply can't change the decimal separator in the custom format number box. The only idea I've had is to make a custom font that switches dot and comma around but I haven't found a straightforward way to do that yet. Any ideas?

  4. #4
    Registered User
    Join Date
    08-08-2005
    Posts
    1

    setting in excel

    hi,
    you may try changing the setting for decimal places in tools/options/international from commas to dots and save the document in this setting.
    hope this's helped......

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1