Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Specifying a Language for the TEXT Function.

Specifying a Language for the TEXT Function

by Allen Wyatt
(last updated July 20, 2019)

2

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. Choose Cells from the Format menu. Excel displays the Format Cells dialog box.
  3. Make sure the Number tab is selected.
  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

The inclusion of a language specifier code in your TEXT format will work in at least Excel 2002 and Excel 2003. It may work in earlier versions, as well, but hasn't been tested.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3299) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Converting Text to Numbers

Import information from a program external to Excel, and your numbers may be treated as text because of the way that the ...

Discover More

Filling a Drawing Object

Want to add some spice to the graphics in your worksheets? There are many colors and effects in Excel that allow you take ...

Discover More

Floating Information in a Frozen Row

You can freeze information in rows or columns using one of the built-in features of Excel. As you move up or down in the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (menu)

Returning the Left-most Characters

When working with text in a formula, you may need to extract the left-most characters from a string of text or from a ...

Discover More

Specifying Proper Case

If you need to change the case of letters in a cell, one of the functions you can use is the PROPER function. This tip ...

Discover More

Returning an ANSI Value

Need to know the character value of the first character in a string? It's easy to do, without using a macro, by using 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

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. Maximum image size is 6Mpixels. 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 eight less than 8?

2019-11-22 13:55:27

Guido van Es

Hi, on some posts I found out that the letter e replaces the yyyy, jjjj and aaaa etc as a universal code for the (full) year notation: we're trying that out now. It seems to work: would be the best (easiest, most practical) solution for multilingual users, companies, work groups, etc.

I also found another solution which is only using dd and mmm but then leaving the jjjj or yyyy out, and instead using the YEAR function as a separate formula within the longer array. A bit more hassle, but it works, as well. Example (for Spanish, using formulas in English):

="De "&(TEXT(B56;"[$-0C0A]dd mmmm yyyy"))&" hasta "&(TEXT(B365;"[$-0C0A]dd mmmm yyyy"))&" ("&I1&" días de viaje)."
Becomes:
="De "&(TEXT(B56;"[$-040C]dd mmmm"))&" "&YEAR(B56)&" hasta "&(TEXT(B365;"[$-040C]dd mmmm"))&" "&YEAR(B365)&" ("&I1&" días de viaje)."

But the best solution still seems:
="De "&(TEXT(B56;"[$-0C0A]dd mmmm e"))&" hasta "&(TEXT(B365;"[$-0C0A]dd mmmm e"))&" ("&I1&" días de viaje)."

I hope this answer finds you and next readers well :-)
I am not sure if it works for languages that don't use/read dd as day and mmm as month: in our case working mostly with English, Spanish and Dutch it works because it's the same letters...

Cheers,
Guido


2019-11-22 12:06:50

Guido van Es

Hi Allen,

Interesting post and I've used it, too. However a problem has popped up and I can't find anything about it. I hope you can help.

I have colleagues in different parts of the world, working in different languages. We all use Excel on a daily basis, in five languages.

I am the one always coming with improvements to our standard (shared) Excel templates. In the latest version, I used the TEXT function to fill in a travel period automatically: "from 1 October 2019 to 20 October 2019". With the language codes it seemed to be working fine in every language. However, we now found out that the language of Excel (Office) itself, so the locale settings of each colleague, is causing trouble.

On my Dutch computer, I made the formula like this: ="De "&(TEKST(B56;"[$-0C0A]dd mmmm jjjj"))&" hasta "&(TEKST(B365;"[$-0C0A]dd mmmm jjjj"))&" ("&I1&" días de viaje)." (This is an example in Spanish)
However, my colleagues aren't seeing the years correctly: they see "jjjj".
When they change it to "yyyy" it's fine for them (so I thought, let change it all to the unversal language English, jeee!), but then I see yyyy instead of the year, arghhh....

Is there some sort of universal character to use?? Thanks a lot, I'm sure there are more people suffering this...

Best regards,

Guido


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.