Currency format conversion

Question

My problem: Converting an Excel range from a currency format (USD) to another (EUR) and vice versa.

USD format in Excel looks like 123,456.00
EUR format in Excel looks like 123 456,00

where in USD values comma is the thousand separator and . is the decimal point and in EUR values space is the thousands separator and comma , is the decimal point.

I've tried some coding in VBA but I'm a newbie in this area.

Any help to build a VBA macro code for using in Excel spreadsheets?

Answer 1

This UDF will do the job. Copy and paste into a module:

Function ConvertUSDToEUR(Amount As Range) As String
Dim TempStr As String: TempStr = Amount.Value
TempStr = Replace(TempStr, ",", " ")
TempStr = Replace(TempStr, ".", ",")
ConvertUSDToEUR = TempStr
End Function

Output:

Answer 2

With a value in A1, In B1 enter:

=TEXT(A1,"#,##0.00")

and in C1 enter:

=SUBSTITUTE(SUBSTITUTE(B1,","," "),".",",")

For example:

NOTE:

It is also possible to transform the values "in place", but this would require VBA.

EDIT#1:

Here are two macros that will change formatting "in place". Thus the results will still be numbers, but the appearance will change. Say we have a list of numbers (not formulas):

Select the values and run this macro:

Sub MakeUS()
    Dim r As Range
    For Each r In Selection
        r.NumberFormat = "#,##0.00"
    Next r
End Sub

to produce:

and this macro:

Sub MakeEUR()
    Dim r As Range, v1 As String, v2 As String, DQ As String
    DQ = Chr(34)
    For Each r In Selection
        v1 = Format(r.Value, "#,##0.00")
        v2 = Replace(Replace(v1, ",", " "), ".", ",")
        r.NumberFormat = DQ & v2 & DQ & ";;;"
    Next r
End Sub

to produce:

If the column contained formulas, the approach would be slightly different.