Change decimal separator with macro in Excel?

Question

I want to change decimal separator from comma to point. How can I do this with macro?

Solution 1

Actually you can do it.

Sub Macro1()
    With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = ","
        .UseSystemSeparators = false
    End With
End Sub

Importante Note:

This change will take effect only inside Excel and on ALL Excell files open. And the next time you open Excel, this settings will be active.

You could record the state of this settings before changing them so that you can return the application to its original state.

Solution 2

You can't.

The decimal separator is a part of your operating system's regional settings.

It cannot be changed on an application (Excel) level or a file (Excel workbook) level.

If you have problems with the decimal separator in a file that you want to use in Excel, please edit your question to state the real issue.

Your system may use the . as a decimal, but the file you want to import uses a comma. Open the file in a text editor and replace commas with dots and dots with commas.

• for example: replace all commas with # (or another character that is highly unlikely to appear anywhere in the file)
• then replace all dots with commas
• then replace all # with dots.

Save the file and then import into Excel.

Comments

[Przemyslaw Remin]

How do you know it? I have problem in VBA arrays I have a comma as separator although ? Application.DecimalSeparator returns dot. Do you speak about that?

[teylyn]

@PrzemyslawRemin yes. Thy system separator for decimal in your system is dot. The list separator and the decimal separator are different things. In a system with dot as the decimal, the list separator (which is used in arrays) is typically a comma. In systems where the decimal separator is a comma, the list separator is typically a semicolon.

[Przemyslaw Remin]

Thank you. Is there any elegant simple way to find out what is user's list separator? I am doing it the twisted way: stackoverflow.com/a/47079612/1903793

[EmRoBeau]

is this different from changing the advanced settings for your excel sheet? I want the macro to run in excel tables that are embedded in word and not make the changes in all other excel files.

[Andrés Fernández]

Thanks, changing the decimal separator in the Regional Settings in Windows instead of the Excel options worked.