Forum Discussion

hynguyen's avatar
hynguyen
Iron Contributor
Jul 11, 2020
Solved

French and English number formatting at the same time

I am asked by coworkers to find a solution for their Excel reports. They need to prepare a same Excel report but one will be in French and the other will be English, both of which must be available f...
excel
  • JKPieterse's avatar
    JKPieterse
    Jul 13, 2020

    hynguyen You could consider using a user-defined VBA function like this one:

    Function FormatFrench(Src As Range)
    Dim vData As Variant
    Dim lRow As Long
    Dim lCol As Long
    vData = Src.Value
    
    For lRow = LBound(vData, 1) To UBound(vData, 1)
        '1,234,567.00 €
        '1 234 567,00 €

        For lCol = LBound(vData, 2) To UBound(vData, 2)
            If IsNumeric(vData(lRow, lCol)) Then
                If vData(lRow, lCol) > 0 Then
                    vData(lRow, lCol) = Format(Abs(vData(lRow, lCol)), "#,##0.00 €  ")
                Else
                    vData(lRow, lCol) = Format(Abs(vData(lRow, lCol)), "#,##0.00 € -")
                End If
                vData(lRow, lCol) = Replace(vData(lRow, lCol), ",", " ")
                vData(lRow, lCol) = Replace(vData(lRow, lCol), ".", ",")
                'vData(lRow, lCol) = "'" & vData(lRow, lCol)
            End If
        Next
    Next
    FormatFrench = vData
End Function
JKPieterse's avatar
JKPieterse
Silver Contributor
Jul 13, 2020

hynguyen You could consider using a user-defined VBA function like this one:

Function FormatFrench(Src As Range)
    Dim vData As Variant
    Dim lRow As Long
    Dim lCol As Long
    vData = Src.Value
    
    For lRow = LBound(vData, 1) To UBound(vData, 1)
        '1,234,567.00 €
        '1 234 567,00 €

        For lCol = LBound(vData, 2) To UBound(vData, 2)
            If IsNumeric(vData(lRow, lCol)) Then
                If vData(lRow, lCol) > 0 Then
                    vData(lRow, lCol) = Format(Abs(vData(lRow, lCol)), "#,##0.00 €  ")
                Else
                    vData(lRow, lCol) = Format(Abs(vData(lRow, lCol)), "#,##0.00 € -")
                End If
                vData(lRow, lCol) = Replace(vData(lRow, lCol), ",", " ")
                vData(lRow, lCol) = Replace(vData(lRow, lCol), ".", ",")
                'vData(lRow, lCol) = "'" & vData(lRow, lCol)
            End If
        Next
    Next
    FormatFrench = vData
End Function
  • hynguyen's avatar
    hynguyen
    Iron Contributor
    Jul 14, 2020

    JKPieterse Thanks a lot for your great idea. I did not think of UDF at all!

    Your UDF works perfectly with a continuous range but I don't know why it does not work with a single cell. I tried to fix the array parameter in the code unsuccessfully (my array knowledge is bare). So based on your code, I rewrite a new function for only a single cell instead 🙂

    I will give my coworkers both functions for their preference. If you ever read this and could teach me on how to change the code to capture also the case of single cell, it would be greatly appreciated.

    Thanks again for your help!

Resources