TEXT function - internationalization hints?

[Rijnsent]

In my Excel model I'm using some TEXT functions to format certain elements so they get wrapped in a text string. Currently I'm trying to build something that also works in various localizations and start noticing that the format string differs massively between countries/localizations. I'm using it e.g. as:

=TEXT(A1,my.NumberFormat1)
=TEXT(A2,my.NumberFormat2)
=TEXT(A3,my.DateFormat1)
=TEXT(A4,my.DateFormat2)

An example goal is a text/string in a cell: "This value is built up as € 3.000 + € 125 equals 15,5%", where I'm using the formatting for all numbers (so building up the string like so: =B1&C1&D1 - where D1 refers to such a TEXT function). For those formats I have some named ranges (my.NumberFormat1 etc.).

Some examples of formatting I'm using (to input in the TEXT function):
\€ #.0 \m\i\l
0%
0,0

What I'm now bumping into is localized settings: it's quite hard to pick up local number (or date) formats and process them into a text string - with an Estonian client the TEXT function is throwing a #VALUE as my formatting string doesn't work over there. Is there here somebody with some good examples on how to do that/good insights on how to approach this?

 

[PeteWright]

Hello @Rijnsent

I can't figure out what you actually mean.
Do you need to type in "your language" and make it work everywhere else?

Then you shoud try adding your locale to the number formatting.
Taken from your profile you'll need [$-nl-NL]

Could you provide some examples of your data?

 

[Rijnsent]

Okay, let me try to rephrase it:
A weird example I saw today (and fixed in another way): TEXT(DAY(A1);"00") will on my system show the day of that date with 2 digits (so e.g. 04 or 14), but that doesn't work on the machine of the Estonian I checked: there that formula always returns "00". I know that date formatting with TEXT is hell: for me the 4-digit-years are "jjjj", for English people it's "yyyy" and French has to be "aaaa", just to name a few.

In my sheets I want to construct a string/text for the user, e.g. "The value is € 30.14 and increased by 2.0%". Those 2 values are in cells, but if you simply concat it in a string, they won't get formatted properly. The first number I want a currency symbol and 2 decimal places, for the percentage I want 1 decimal. So I'm working with TEXT functions to format the values that are in cells. In my Dutch environment, it would e.g. be TEXT(A1,"\€ #.0") and TEXT(A2,"0.0%"). However, those strings don't work in other environments. So that's why I'm wondering if anyone has an idea how to go about this - I'm now trying to avoid the TEXT function .