Excel Tips and Tricks #507 - Using Excel internationally part 1: why some functions are not good travellers

If you been to Europe, you may have noticed that numbers are not always written in the same way as they are in the UK. In the UK, you might travel 12,345.67 km in a year, but in France you would travel 12.345,67 km - the comma and full stop have swapped roles.

Most of the time, Excel handles this for you. Standard number formatting uses the regional settings on your computer, so the same underlying value can be displayed in the appropriate format for the country or region you are working in.

However, not all functions are quite so worldly-wise.

The TEXT function is a useful way to turn numbers, dates and times into labelled outputs, dynamic headings, file names, report captions and export-ready strings. It can also be a source of subtle internationalisation issues because its format instructions depend on the regional and language settings of the Excel environment in which the formula is interpreted.

That matters when a workbook is built in one country and opened, reviewed or maintained in another. A formula that works in a UK build may not behave as expected for a colleague based in Austria. In some cases, the result may simply be poorly formatted text but in more serious cases, a model may produce different outputs entirely leading to incorrect decisions being made.

1. Meet the TEXT function

The TEXT function lets you take a value that Excel stores as a number, date or time and display it as a formatted text string. Its basic structure is:

=TEXT(value, format_text)

The first argument is the value you want to format. The second argument is the format pattern you want Excel to apply, written inside quotation marks. For example, =TEXT(A1,"£#,##0.00") could be used to show a number as a currency label, while =TEXT(A1,"dd mmmm yyyy") could be used to show a date in a long-written format.

See this article on Mastering Number Formats for more information on the different format arguments you can use.

The TEXT formula is particularly useful when you want to combine a value with words, such as building a dynamic title or creating a report label.

For example, if cell A1 contains the value 12345.67, then the formula: 

="I have travelled " & TEXT(A1,"#,##0.00") & " km"

can create the label I have travelled 12,345.67 km. Without TEXT, Excel would display an unformatted number which, especially for large numbers, can be difficult to read.

2. When TEXT goes to Europe

In the example below, I have used the TEXT function in cell B1 to create the label I have travelled 12,345.67 km for a UK version of Excel:

If I now open the same file in a European version of Excel I get the text I have travelled 12345,67000 km instead because it is reading the “,” in the format as the decimal separator rather than the thousands separator:

I think you’ll agree this is much less readable.

So, what can we do about it?

One way to make the formula more robust is to calculate the decimal and thousands separators dynamically, rather than hard-coding them. In the example below, the decimal separator is calculated in E1 and the thousands separator in E2. The formula then uses those cells in place of the fixed comma and full stop, so the output can adapt to both UK and European regional settings.

3. When dates get lost in translation

In Austrian regional settings, you can use jjjj to return the four-digit year from a date. For example, if A1 contains a date, the formula =TEXT(A1,"jjjj") can return the year as text.

Where jjjj is the equivalent of yyyy in the UK, which returns the 4-digit year.

However, opening the file with UK regional settings does not return the year, instead it returns jjjj. If you were using the value in B1 as a lookup value in a further calculation, the this would result in errors in your workbook.

To avoid this issue, you can instead use the formula:

=TEXT(YEAR(A1), "0000")

Which will produce the same result in all countries.

Conclusion

The TEXT function not a good traveller and should probably stick to staycations, along with its similarly badly behaved cousins such as VALUE and DATEVALUE. If you do need to use it in an international workbook then be careful, make it as dynamic as possible and add error checks to prevent it ruining your holiday.