A question that crops up occasionally is how to format numbers with a dollar symbol.

A quick search reveals the following, generally accepted answer:

Text(524324.2342, "[$-en-US]$#,##.00")

As the screenshot beneath shows, this technique works. Therefore, what's wrong with this approach?

Equally, if were to set the language to Spanish, the € symbol appears.

In cases where we format existing currency values from a data source, this behaviour causes a major problem because an incorrect currency symbol completely changes the meaning of the data. 

So how do we correct this? One answer is to specify "en-US" as the third argument in the call to Text. This is the value that specifies the language for the format.

Text(524324.2342, "[$-en-US]#,##.00", "en-US")

Another approach is to not include the currency symbol in the call to Text, but to instead prefix the value with a hard-coded $ symbol. 

"$" & Text(524324.2342, "[$-en-US]$#,##.00")

The additional reason why this method works very well is because if the end-user were using a device with a European language (such as French, Spanish, or German), the numeric output would be formatted in the expected way for those regions - that is, a number that uses the comma as the decimal point separator.

Conclusion

When attempting to format numbers with a dollar symbol, app builders may call the Text function in a way that doesn't work correctly in non-US regions. This post described the ways in which we can avoid this problem.

•   Categories: 
• text