Numbers - How to Format Numbers with a Dollar Symbol the Right Way

To format a number with a dollar symbol, follow the method in this post to ensure compatibility with users from different regions.

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?

How to correctly format numbers with the Dollar Symbol
The reason why this is problematic is that it can provide unexpected results for users from different regions. For example, if we were to run the app with the browser language set to "English (United Kingdom)", this is what appears.

As the screenshot beneath shows, the pound (£) symbol appears rather than the dollar symbol.

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.


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