Excel Number Custom Format

Question

I am working on an Excel worksheet, where I have two different currencies. One is the Australian dollar (AUD/$)) and the other is Indian Rupees (INR/Rs).

The requirement is that in the same Excel worksheet, the format of display for these two currencies should be per the national standards i.e. US dollar format grouping for Dollars and Indian Rupees format grouping for Rupees.

I, therefore, cannot use the customise format, under additional settings under region and language.

The end result expected is as following:

|Column A | Column B | |AUD | INR | |$ 98,765,432.10 | Rs 9,87,65,432.10 |

I tried using cell formating, (Ctrl+1 on windows Excel) and adding the following code from the internet:

*[>=10000000]"Rs "##\,##\,##\,##0;[>=100000]"Rs " ##\,##\,##0;"Rs "##,##0*

Here I have observed that this code works for positive numbers only and fails for any negative number.

The standard for writing this code is; format for Positive; format for Negative; format for Zero; format for Text Therefore;

[>=10000000]"Rs "##\,##\,##\,##0; --> is for positive number only [>=100000]"Rs " ##\,##\,##0; --> is for negative number only "Rs "##,##0 --> is for zero Nothing for text --> for text (nothing specified)

I fail to understand that how the above code works!!!

My partial understanding is that the code is using conditional formating which allows for two conditions testing only e.g. [Blue];[Red] So two requests:

I will appreciate it if somebody can help to understand the above codes working against the standard.
Help me solving my difficulty as explained above.

Regards PSS

The conditions (within brackets) override the positive/negative/zero defaults; but there are still only 3 numeric conditions allowed. Since you require more than three, you should look at Condtional Formatting which, unless you have a very old version of Excel, can have more than three conditions. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 22, 2021 at 11:38

Dharman · Accepted Answer · 2022-02-26 15:01:03Z

I don't know the standard for Rs well, but if you happen to still be thinking about this ...

Suggested

If you are able to separate your data (numbers/calculations) from the presentation of that data ... you can put all the numbers in one column, and then use TEXT(), SUBSTITUTE() and CONCAT() to format your data for presentation:

A1 = 987654532.10

B1 = ="Rs "&SUBSTITUTE(TEXT(D2,"## ## ## ###.00"), " ", ",")

From what I've tried, that looks like it will correctly display both positive and negative numbers. It also drops leading digits and provides the decimals that were missing from the solution you posted.

The big down side is that this provides B1 as a text cell. It's really only good for presentation. Calculations have to be on A1 ... but at least B1 will reflect those appropriately.

The second down is you lose any auto-coloring that would appear on negative numbers in A1. To apply red to B1 on negative numbers ... set a Conditional Format that depends upon A1 < 0.

OTHER STUFF

Basically, it looks like Excel only likes one kind of digit grouping: three digits at a time. In the number format text, commas don't actually tell Excel to 'put in a comma.' Commas normally mean 'divide by 1000'.

MS provides an example like 72567 formatted as "#," displays as 73 (Excel rounds up here). "#,###" would yield 72,567 ... but so does "#,#" or "##,00". Ugh.

Its hard (pretty much impossible, in this case) to re-group the digits using the number formatting interface.

In the format text you posted, the author decided to force Excel to place commas in specific locations. They did that by providing an explicit format for a limited range of positive numbers. Putting the , in the format string forces Excel to insert a comma no matter what. The author gained/ensured exact placement, but they had to account for numbers of different lengths. That means they couldn't keep all four of pos/neg/zero/text. Instead, they settled for accurate presentation of only three ranges (and text):

'big' (> 10,000,000)
'kinda big' (1,000,000)
'not big but positive' (>0)
text

Notably, that means they missed a bunch of numbers: all negatives, all above 10 million, and all numbers with six digits above zero: 100,000 to 999,999 accidentally include a (forced) comma in front, even if not needed. 765432.10 displays as Rs ,7,65,432.10.

Collectives™ on Stack Overflow

Excel Number Custom Format

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related