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.

Specifying a Language for the TEXT Function

by Allen Wyatt
(last updated November 28, 2015)

10

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:

  1. Select the cell (or cells) you want to format.
  2. Display the Home tab of the ribbon.
  3. Click the small icon at the bottom-right corner of the Number group. Excel displays the Number tab of the Format Cells dialog box.
  4. Click Date at the left side of the dialog box. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. Using the Locale drop-down list, choose a country or region that uses the date format you want to use. In the case of Mikael's need, picking English (United States) is a good choice. Excel modifies the date formats available to you.
  7. Select the desired date format.
  8. Click on OK.

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:

=TEXT(A1,"[$-409]mmmm, yyyy")

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:

Code Language
0436 Afrikaans
041C Albanian
045E Amharic
0401 Arabic
042B Armenian
044D Assamese
082C Azeri (Cyrillic)
042C Azeri (Latin)
042D Basque
0423 Belarusian
0445 Bengali
0402 Bulgarian
0403 Catalan
045C Cherokee
0804 Chinese (Simplified)
0404 Chinese (Traditional)
041A Croatian
0405 Czech
0406 Danish
0465 Dhivehi
0413 Dutch
0466 Edo
0C09 English (Australian)
1009 English (Canadian)
0809 English (U.K.)
0409 English (U.S.)
0425 Estonian
0438 Faeroese
0464 Filipino
040B Finnish
040C French
0C0C French (Canadian)
0462 Frisian
0467 Fulfulde
0456 Galician
0437 Georgian
0407 German
0C07 German (Austrian)
0807 German (Swiss)
0408 Greek
0447 Gujarati
0468 Hausa
0475 Hawaiian
040D Hebrew
0439 Hindi
040E Hungarian
0469 Ibibio
040F Icelandic
0470 Igbo
0421 Indonesian
045D Inuktitut
0410 Italian
0411 Japanese
044B Kannada
0471 Kanuri
0460 Kashmiri (Arabic)
043F Kazakh
0457 Konkani
0412 Korean
0440 Kyrgyz
0476 Latin
0426 Latvian
0427 Lithuanian
042F Macedonian FYROM
043E Malay
044C Malayalam
043A Maltese
0458 Manipuri
044E Marathi
0450 Mongolian
0461 Nepali
0414 Norwegian Bokmal
0814 Norwegian Nynorsk
0448 Oriya
0472 Oromo
0463 Pashto
0429 Persian
0415 Polish
0416 Portuguese (Brazil)
0816 Portuguese (Portugal)
0446 Punjabi
0418 Romanian
0419 Russian
044F Sanskrit
0C1A Serbian (Cyrillic)
081A Serbian (Latin)
0459 Sindhi
045B Sinhalese
041B Slovak
0424 Slovenian
0477 Somali
0C0A Spanish
0441 Swahili
041D Swedish
045A Syriac
0428 Tajik
045F Tamazight (Arabic)
085F Tamazight (Latin)
0449 Tamil
0444 Tatar
044A Telugu
041E Thai
0873 Tigrigna (Eritrea)
0473 Tigrigna (Ethiopia)
041F Turkish
0442 Turkmen
0422 Ukrainian
0420 Urdu
0843 Uzbek (Cyrillic)
0443 Uzbek (Latin)
042A Vietnamese
0478 Yi
043D Yiddish
046A Yoruba

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Excluding a Specific Add-In at Startup

Got an add-in that you don't want loaded each time that Excel starts up? Here's a few ways that you can exclude it.

Discover More

Squaring Table Cells

Inserting a table is fast and easy in Word. You may want to make sure that the cells in the table are as square as possible. ...

Discover More

Displaying a Count of Zeros on the Status Bar

Excel allows you to display the results of several common worksheet functions on the status bar. The available functions are ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (RIBBON)

Counting with Formulas

When you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip ...

Discover More

Using the XIRR Function

One of the financial worksheet functions provided in Excel is the XIRR function. This is used to figure out an internal rate ...

Discover More

Returning the MODE of a Range

The MODE function is used to determine the most frequently recurring value in a range. This tip explains how to use the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 9?

2017-02-17 03:18:57

Edward Chan

Referring below number format :Selection.NumberFormatLocal = "#,##0.00_);[Red](#,##0.00)"1. [Red] cannot be recognized in Chinese window (will use [紅色] instead)2. How to re-write these codes to be able to be recognized in both English and Chinese Windows as both Windows are being used in our group of staff.


2017-02-07 12:06:32

Peter

Edward

Maybe something like this


Sub t()
Dim myRed As Variant, c As Range
myRed = RGB(255, 0, 0)
For Each c In Selection
c.NumberFormatLocal = "#,##0.00_)"
If c < 0 Then
c.Font.Color = RGB(255, 0, 0)
End If
Next c
End Sub

HTH


2017-02-07 01:49:41

Edward Chan

What about using vba to specify font color as below :

Selection.NumberFormatLocal = "#,##0.00_);[Red](#,##0.00)"

"Red" is not recognizable in Chinese language Window/Excel

Grateful if you could advise.

Thx in advance


2016-08-19 01:12:25

Aner

Good but you can do this directly from the format cell option (custom date).


2016-07-21 17:52:11

Douglas

Very nice and clean globalization feature I did not know, thanks for sharing.

I also come across the problem of using english formats like text(a1;"yyyy-MM-dd") but some of the users has excel in spanish they do not get the correct dates.

My solution to this issue (without VBA) is testing each format (only the year part):

A1: has the today() function
B1: has the english format for years value(text(a1;"yyyy"))
C1: has the spanish format for years value(text(a1;"aaaa"))

One of the cells B1 or C1 would throw an error so I can get the language being used.


2016-06-08 10:34:30

Gabriella

It's great formula that I can use it in Tools but I have some issue, I have different régions using these Tools some with french regional settings & other English.

I used the code "040C" to get the date in French, output went wrong (dd avril yyyy)for french regional settings as french uses "j" & "a"
i.e. = if(Language="FR",TEXT(a1,[$-0C0A]dd mmm, yyyy) get answer (dd avril, yyyy)for french desktop
Is there a way to get the date. Thanks in advance


2016-05-02 14:32:12

Rodolfo

For hours tried to format invoices showing on the detail the month and year corresponding to the rent, not the date of the invoice, and kept changing for English to Spanish (my language).
Your help with the TEXT(a1,[$-0C0A] saved me

Thank you

ririarte


2016-04-22 06:07:00

Reggie

Do you have a tip for the following issue:
Excel workbook is used from several environments, one of tchem uses English date formats - DDMMYYY, and one of them French - JJMMAAAA.
I would like to use one excel function for both environments. Is it possible without custom VBA functions?


2016-03-17 10:44:53

Marc

Many thanks for this post!!!!!
I have been struggling for years with this problem and I came up today on your tip! You really made my day :-)
Cheers!


2016-01-07 00:34:04

Farag

I am from Benghazi, Libya
Thanks a lot for this tip , it was really useful for my excel applications.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing