TEXT() function sensitivity to separators

Question

I have a formula that I would like to be sensitive to the decimal and thousands separators. A simple such formula looks something like this in English:

="The result is " & TEXT(A17, "#,##0")

Now, I'm adding localization, courtesy of some custom LAMBDA functions I wrote. The same formula looks something like this:

=GetLocaleString(RESULT_IS) & TEXT(A17, GetLocaleString(INTEGER_FORMAT))

The strings associated with RESULT_IS and INTEGER_FORMAT vary from language to language.

I would prefer not to have to figure out the format for each and every language, especially when there are variations within languages. What I would really like is a format that is sensitive to the separators that Excel is using, whether they come from the system or are overridden in "Options | Advanced". For the life of me, I can't find anything like that. Does such a format exist?

Answer 1

Does the worksheet function =NUMBERVALUE ( ) help?

Converts text to a number, in a locale-independent way.

Answer 2

That's one of those VBA functions that should really be available at the formula level. 😎 LAMBDA functions are great, but the inability to query the environment is a problem for localization.

Thanks for your time.

Answer 3

Macro can get list separator in system. But I haven't found a way to get it using formula.

=======================

Application.International(xlListSeparator)

=======================

Answer 4

It's a start, but as you say it applies only to "." and ",", when there could be " " as well. I'll assume, based on your reply, that there's no truly generic solution.

Answer 5

You may try this one. It only works for "." and "," as list separator.

="The result is " & TEXT(A17, "#"&IF(MID(1/10,2,1)=".",",",".")&"##0")