CODEWITHSUNDEEP
exceldecimalseparatorvba
user3215646
user3215646

Reputation: 21

Change decimal separator with macro in Excel?

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

Upvotes: 2

Views: 21338

Answers (3)

CaBieberach
CaBieberach

Reputation: 1768

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.

Upvotes: 5

WolfsBane
WolfsBane

Reputation: 1

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

Upvotes: 0

teylyn
teylyn

Reputation: 35915

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.

Upvotes: 1

Related Questions