 ##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

# Substitute or replace?

0

I'm working on formatting imported financial numbers from software in Excel. The current format uses dots as thousand separators and commas as decimal separators, such as "1.900,10" or "900,00".

Excel doesn't recognize this as financial data. To correct this, I need to replace the first dot " . " with a comma " , " and the last comma "," with a dot "." in all instances (e.g., 100, 1000, 10000, etc.).

How can I efficiently perform this transformation on a large dataset that is updated monthly in Excel?

### Discussion

John_Ru (rep: 5572) Aug 15, '23 at 10:03 am

0

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).

0

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   :-)

### Discussion

@Willie - that will work well except if the comma and full stop (period) are used as grammatical marks in any text columns (which is perhaps unlikely in financial reports).
John_Ru (rep: 5572) Aug 3, '23 at 3:21 am