For me it has happened many times, that decimal separator is incorrect.

Comma is thousand separator and dot decimal separator. For me, the thousand separator is not needed and decimal separator should be comma. This is my view, someone might disagree.

First, check the number formatting settings in operating system and in Excel.

Take control panel, and number formats under clock and region header.

Select additional settings.

You will find here decimal separator (decimal symbol).

In Excel:

In Excel decimal and thousand separators are in file – options – advanced page.

If “use system separator” is unticked, then

Excel understands 1,500 to be 1500.

When “use system separator” is ticked, then

Excel calculates correctly, to my purposes.

Check the number formatting for the cell by home – number – format cells. Shortcut to this transaction is control + 1.

One option is to manually remove first the comma and the replace dot with comma. You can do this control + F and then control + H.

You can also record macro, and play the macro whenever needed. Here is the recorded VBA. I did not write this VBA, but simply recorded the macro and captured VBA.

Sub Z_commadot ()

‘

‘

    Selection.Replace What:=”,”, Replacement:=””, LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

    Selection.Replace What:=”.”, Replacement:=”,”, LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

‘         data – text to columns part

        Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 1), TrailingMinusNumbers:=True

End Sub