Localization of formulas in Excel

The name of an Excel file
Excel

In an Excel sheet I needed the name of my Excel file as a value in a cell. For a problem like this, Google is your friend, so I did a search for “get the name of my workbook into an excel cell“. And of course the first link was a good one, as it pointed me to Excel formula: Get workbook name only. The answer didn’t look very simple by the way:

But I faithfully pasted it into my sheet. Unfortunately it didn’t work! Owww, of course: I was working in the Dutch version of Excel and that wants the formulas to be in “Dutch” too. Apparently Microsoft thinks the users can understand all the math in those difficult expressions, but the English words need to be translated for them.

Localization

With some trial and error I translated the formula into Dutch:

Personally I think the Dutch translation is more difficult to understand than the English version, but it worked. That is the main thing.
But when I opened the workbook on a PC with an English version of Excel, the Dutch formula didn’t work of course!

I started looking for a solution. First of all, I pasted the original formula into the cell next to the one that didn’t work. It worked fine on my English language computer. But I expected it would fail again on the Dutch computer. To my great amazement that was not the case: it worked without a problem and the formula had been translated automagically!


So I could have saved myself the trouble of translating the formula? If you look closely, you will notice this is not exactly the same as my own translation: the quoted texts were left untranslated! And yet it works. Apparently the Dutch Excel doesn’t mind whether you write “filename” or “bestandsnaam”.

Translated backwards

Then I took a detailed look at what the English version of Excel had done with my translation. Hadn’t that been translated back into English correctly? Well, almost:

Again the formula had been translated, but not the quoted strings. And the English version of Excel hasn’t got a clue about what a “bestandsnaam” is.

Conclusion

Apparently formulas are stored internally in English or in a binary format, but the content of strings remains untouched. It is not a problem to get formulas from the Internet, but do paste them into a spreadsheet that is in the same language as your formula.

And don’t fall into the trap of using Dutch terms to indicate which information you want to request in the function CELL( … ); the English terms work in the Dutch version of Excel too and your spreadsheet will be usable in all languages.

I would be curious to know whether this tip also applies for the German and Portuguese versions of Excel. Can someone please experiment with that and let me know in the comments below?

 

This post is also available in: Dutch


Leave a Reply

Your email address will not be published. Required fields are marked *