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 for editing at the same time (which is important because it rejects the option to change Excel Options/Advanced to not use system separators).

- The French number format should show a space as thousand separator, comma as decimal separator, only 1 digit after the decimal separator, and negative number should be wrapped in single bracket, e.g. English -105.6 and -2,345.9

French should be (105,6) and (2 345,9)

- Currently their numbers contains not more than 6 digits before decimal separator but this may change in the future.

- The number formatting for French is simply for presentation purpose, i.e. no calculation will be used on them.

 

My current solution to get French number formatting without changing Excel settings is to add a formula =IF(AND(ABS(English value cell)<1000,English value cell<0),SUBSTITUTE(TEXT(-English value cell,"(#.#)"),".",","),English value cell*10) and custom format them as # ##0","0;(# ##0","0);- 

 

However, I expect there may be some better solutions for this problem from the experts in this community. Thanks in advance.

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

2 Replies

  • 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