Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Specifying a Language for the TEXT Function.
by Allen Wyatt
(last updated November 28, 2015)
Mikael uses a Danish version of Excel. If he uses the TEXT function to format dates, as in TEXT(A1,"mmmm, yyyy"), the textual format returned shows the months in Danish. He would like the months returned in English instead, and wonders how he can instruct the TEXT function as to which language it should use.
There are a couple of ways you can approach this problem. The first is applicable if you simply need to display a date (and nothing else) in a cell—simply don't use the TEXT function. You can easily format a cell to display a date in any language; follow these steps:
Figure 1. The Number tab of the Format Cells dialog box.
If you are using the TEXT worksheet function because it is part of a larger formula, then you can instruct the function itself to use a different language for its output. You do this by including a language code (formally called an LCID) within brackets, in this manner:
Note that the bracketed code is within the format string, and the code itself is preceded by a dollar sign and a dash. The code is either three or four hexadecimal digits. (Actually, all LCIDs can be expressed in four hexadecimal digits, but if the leading digit is a zero, you don't need to include it.) The example above shows how to express results in English, but you can pick any of a wide range of countries:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11782) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Specifying a Language for the TEXT Function.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
You may use Excel's trigonometric functions to do some quick calculations, and suddenly notice that the results in your ...Discover More
Ever want the IF function to only return a value if the condition it is testing is true, and not if the condition is ...Discover More
Some users have reported problems using the EOMONTH function in later versions of Excel, beginning with Excel 2007. The ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.