Jul 11 2020 12:27 PM
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.
Jul 13 2020 07:17 AM
Solution@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
Jul 13 2020 09:10 PM
@Jan Karel Pieterse 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!
Jul 13 2020 07:17 AM
Solution@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