Substitute or replace?

Hi Matthias and welcome to the Forum.

Not sure which version of Excel you have (you should add that to your Profile in this Forum) but you could take two other approaches (if you don't want to chnage your system decimal separator to suit that data).:

If you want a formula, use the NUMBERVALUE function. that has three arguments, as follows:

=NUMBERVALUE(Text,Decimal_separator, Group_separator)

and the Group separator will be for thousands.

If you have say 1.000,34 in cell A7, put this in B7:

=NUMBERVALUE(A7,",",".")

and it will show as 1000.34, which you could format to show a comma (,) as the thousands separator (so 1,000.34).

If you have a large data set (as you suggest) and it's in columns, you can use Flash Fill for a one-off conversion.  Insert a column to the right of a column with 1.000,34 type data. If 1.000.34 is in the top cell of the original column, type 1,000.34 in the cell to its right then Enter. While still in the new column, type Ctrl+E and the all data will be converted (and you can then delete the original column- note that the new column won't update if you change a cell in the left column).

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

Hello Matthias,

Here is an option if you want to use VBA.

In a regular code module add this macro:

Sub Fix_Format()

' find " , " (comma) and change it to an " X "
Cells.Replace What:=",", Replacement:="X", LookAt:=xlPart

' find " . " (period/dot) and change it to a " , " (comma)
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart

' find " X " and change it to a " . " (period/dot)
Cells.Replace What:="X", Replacement:=".", LookAt:=xlPart

End Sub

Select the sheet that needs fixing and then run the macro.

Cheers   :-)