Comma & Dot Seperators

[andy3816]

I saw the post from Gratu and subsequent response from Hot Pepper on how to query the excel installed to see if the European or American format of comma for thousands seperator and dot for decimal seperator or European format dot for thousands seperator and comma for decimal point seperator are being used, this prompted me to post this question.

Can you use VB to force excel to switch, ie check to see if european is being used then switch to American if true, then run some code and switch back when done.

The reason I need this is that I share a very complex workbook globally and the only way our European friends can get some of code and functions to work is to force their compuers to think they are American!!! by altering their international settings.

Any help would sure help international diplomacy!!!

 

[ylijohe]

I guess the most annoying thing is that when copying a number from american table you get it like this 2.17 , but when you pass it to a, say Finnish excell, it shows like a date, like this: helmikuu.17 (February.17). It should be converted to look like this: 2,17 note the comma instead of dot.

The problem would be solved by changing the dots to commas as the decimal separator, the question is how to do it?

Just formatting the cell to "number" will not give right number, but 42767,00 instead of 2,17

By the way, formatting the cell to "date" will result as a date: 1.2.2017 (not february the 17th.)

So, how to change dots to commas when copying data from american tables?

 

[Scott Huish]

andy3816:

This is from the help file under ThousandsSeparator property, is this what you want?

Example
This example places "1,234,567.89" in cell A1 then changes the system separators to dashes for the decimals and thousands separators.

Sub ChangeSystemSeparators()

Range("A1").Formula = "1,234,567.89"
MsgBox "The system separators will now change."

' Define separators and apply.
Application.DecimalSeparator = "-"
Application.ThousandsSeparator = "-"
Application.UseSystemSeparators = False

End Sub

 

[ylijohe]

Thanks.

I run your macro, and now my excel adds "-" instead of ","



How to change the system settings back to normal?

 

[Scott Huish]

You need to change the characters in the macro to what you want.

To put it back:
Application.UseSystemSeparators = True