TEXT formula with custom currency formatting

Background: for various reasons we use Excel to draft our financial statements.
There are a lot of paragraphs that refer to numbers within the financial statements. Easy to deal with by concatenating the text with the references to the numbers.
I use the TEXT formula to make sure that it is formatted properly.

Problem: the TEXT formula shows the thousand seperator when it is less than a thousand.
Example: At year end the entity has an tax loss of R ,500 million (2019: R ,600 million).
The formula that I use is:

Formula:

Please Login or Register  to view this content.

where TextFormat is a named range, which refers to R #,##0 and RoundingText refers to million.

I use named ranges since there are financial statements that have different rounding requirements and it is quicker to change one cell that a bunch of formulas.

Is there a way to use the named ranges in the TEXT formula without it causing a problem with the thousand seperator short of defining another named range for when the amount is less than a thousand?

Can you upload an example workbook?

Is this for google docs, or maybe using macros?

Because some of those functions are not native excel functions...
TextFormat
RoundingText

Please find attached a small workbook with the formula.

I don't replicate the problem. When I enter your formula (adjusting references for the year end values), the number format works correctly. Values larger than 1000 show the thousands separator, while values smaller than 1000 do not. I do note that, if I switch decimal and thousand separator (Text Format = "R #.##0" where my system uses "." for decimal separator and "," for thousand separator) and enter something less than 1 into the year end value, then it will display the leading decimal separator (".2" is displayed).

My only guess is that there is some confusion about what is being used for decimal and thousand separator, but those are things you will need to look at on your own system.

Thank you!
It's the one thing I did not think of checking .... off to go and check my system settings.
And it works if I change my settings