07-11-2020 12:27 PM
07-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.
07-13-2020 07:17 AMSolution
@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
07-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!