Multiple Currency Number Formats in the Same Excel using Custom Number Formatting

Question

I have an excel which has multiple columns of numeric values / amounts, which are all in different currencies in different column. One column is in one currency.

I have my regional setting set to a default currency format, so one column is set. For all other columns I need to use the custom number formatting in excel to set them to their specific currency format.

I under how to add currency symbols to each one to skipping that part here. My issue is with different comma separation for different currencies.

For illustration, I have three formats here:

1. Currency 1: All comma separation three-digits long but without any decimal points.
2. Currency 2: All comma separation three-digits long with 3 decimal points.
3. Currency 3: INR style - Initial three-digit separation and then two digit separation thereafter with decimal.

Currency 3 is done through default regional formatting on windows. I tried following format for currency 1 and 2:

Currency 1:
[>=1000000000]##\,###\,###\,##0;[>=1000000]##\,###\,##0;[>=1000] ##\,##0;##,##0

Currency 2:
[>=1000000000]##\,###\,###\,##0.000;[>=1000000]##\,###\,##0.000;[>=1000] ##\,##0.000;##,##0.000

However, I am getting error in excel that the said formatting cannot be accepted. The format works if I remove the trillion part [>=1000000000]##\,###\,###\,##0 from the custom format. So I am guessing the issue is somewhere there, but I can't figure it out.

Expected Output:

|   Currency 1    |      Currency 2     |     Currency 3      |
|-----------------|---------------------|---------------------|
|       1,000,000 |       1,000,000.240 |        10,00,000.24 |
|           1,000 |           1,000.240 |            1,000.24 |
|   1,000,000,000 |   1,000,000,000.240 |   1,00,00,00,000.24 |
|             100 |             100.240 |              100.24 |
|      -1,000,000 |      -1,000,000.240 |       -10,00,000.24 |
|          -1,000 |          -1,000.240 |           -1,000.24 |
|  -1,000,000,000 |  -1,000,000,000.240 |  -1,00,00,00,000.24 |
|            -100 |            -100.240 |             -100.24 |

Can you help me where is the issue here?

Answer 1

Your custom format has 3 conditions. However, you can only have a maximum of 2 as stated here:

You can only have 2 conditions in a custom number format with each condition separated by a semicolon. i.e. the first condition; the second condition; and then everything else.

The advise given on the same page is to use conditional formating when you have more than 2 conditions:

If you require more conditions you can use Conditional Formatting to override the custom number formatting.

Answer 2

If your standard decimal separator is a period and your standard thousand separator is a comma, and you're grouping digits in groups of three you don't need to repeat the comma in numberformat definition. It is enough to use thousand separator once e.g. #,##0 or #,##0.000.

I'm afraid it's not possible to use Hindi and American number formatting in the same worksheet. In addition to conditional formatting, you can also try to combine NumberFormat with the use of VBA. You can define UDFs (in a standard module) that will generate formatted numbers in text form (in US format).

Function FCur1(x)
   FCur1 = Format(x, "#,##0")
End Function

Function FCur2(x)
   FCur2 = Format(x, "#,##0.000")
End Function

When using functions directly in cells, the format should be General, right-aligned.
You can also define a macro that converts numbers into formatted texts. In this case, select the area you want to format and run the macro:

Sub Apply1()
   Dim rg As Range
   Selection.NumberFormat = "@"
   For Each rg In Selection
      rg.Value = FCur1(rg.Value)  ' or FCur2 as necessary
   Next rg
   Selection.HorizontalAlignment = xlHAlignRight
End Sub