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?

I don't think you can do this with macro. Decimal separator is specified in Windows regional settings. You can, of course, change all commas to points in your file, but if you don't change Windows regional settings this will just make your values invalid as numbers for Excel. — Taosique
– Taosique, Commented Jan 21, 2014 at 8:18

CaBieberach · Accepted Answer · 2014-05-07 07:07:19Z

5

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.

edited May 7, 2014 at 7:07

answered May 7, 2014 at 6:59

CaBieberach

1,7682 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EmRoBeau Over a year ago

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.

teylyn · Accepted Answer · 2014-01-21 08:48:58Z

1

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.

answered Jan 21, 2014 at 8:48

teylyn

36.3k6 gold badges58 silver badges76 bronze badges

4 Comments

Przemyslaw Remin Over a year ago

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 Over a year ago

@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 Over a year ago

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

Andrés Fernández Over a year ago

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

Shog9 · Accepted Answer · 2014-04-20 00:53:41Z

0

Go to Options, and in the Advanced section you will find the option "Use system separator" . Deselect that and put in your own characters.

edited Apr 20, 2014 at 0:53

Shog9

160k36 gold badges237 silver badges242 bronze badges

answered Apr 16, 2014 at 11:22

WolfsBane

1

Collectives™ on Stack Overflow

Change decimal separator with macro in Excel?

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related