Excel VBA Course
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.)

View Course

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?

Your advice is appreciated.

Answer
Discuss

Discussion

Did you see our Answers?
John_Ru (rep: 6142) Aug 15, '23 at 10:03 am
Add to Discussion

Answers

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

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

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

Discuss

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: 6142) Aug 3, '23 at 3:21 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login