2

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

Community
  • 1
  • 1
user3215646
  • 21
  • 1
  • 1
  • 2
  • 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. – ttaaoossuuuu Jan 21 '14 at 08:18
  • You made me sad bro :( – user3215646 Jan 21 '14 at 08:21

3 Answers3

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.

CaBieberach
  • 1,748
  • 2
  • 17
  • 26
  • 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. – EmRoBeau Jun 27 '18 at 14:32
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.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • 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? – Przemyslaw Remin Nov 02 '17 at 15:25
  • @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. – teylyn Nov 02 '17 at 19:53
  • Thank you. Is there any elegant simple way to find out what is user's list separator? I am doing it the twisted way: https://stackoverflow.com/a/47079612/1903793 – Przemyslaw Remin Nov 03 '17 at 08:58
  • Thanks, changing the decimal separator in the Regional Settings in Windows instead of the Excel options worked. – Andrés Fernández Aug 27 '18 at 19:53
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.

Shog9
  • 156,901
  • 35
  • 231
  • 235
WolfsBane
  • 1