Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated December 4, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


38

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. (See Figure 1.)
  4. Figure 1. The Number tab of the Format Cells dialog box.

  5. Click Date at the left side of the 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, 2016, 2019, and Excel in Microsoft 365. 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

Ordering Search and Replace

The wildcard searching available in Word is very powerful. Here's how you can use ordering in your search efforts to make ...

Discover More

Copying a Worksheet

Need to make a copy of one of your worksheets? Excel provides a few different ways you can accomplish the task.

Discover More

Pop-Up Comments for Graphics

Excel allows you to add comments to individual cells in a worksheet, but what if you want to add comments to graphics? ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Phantom Counts

Two common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat ...

Discover More

Determining the Least Common Multiple

Need to figure out the least common multiple of a range of values? It is a snap when you use the LCM function, described ...

Discover More

Using the FORECAST Function

Excel provides a handy worksheet function that allows you to forecast values based upon a set of known values. This ...

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}] (all 7 characters, in the sequence shown) 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 three less than 3?

2022-10-28 10:45:02

Don

I tried =TEXT(H1,"[$-0429]####") to convert 1400 to ۱۴۰۰ (Persian) but Excel 365 still displayed 1400. I had to download and install a persian font and apply it to the cells with the formula.


2022-04-14 13:37:39

Ariel

Thanks a lot! Really helpful!


2022-03-02 10:28:21

J. Woolley

@Olivier
Also, see Control Panel > Clock & Region > Region.


2022-03-01 09:56:03

J. Woolley

@Olivier
See Settings > Time & Language > Region.


2022-02-28 11:52:15

Olivier

Hi Allen. Thanks for the tips. I face a slightly different problem that you might be able to help with.

I have a French laptop that came initially set in French. I changed Windows language to English and everything displays now in English, including MS Office apps.

The odd thing though is that the date format (if I use Text() or the custom format setting) still considers the year/month/day in French (Year='a' instead of 'y'). Hence Text("18-March-2021"; "dd-mm-yyyy") wouldn't work. I must use "jj-mm-aaaa" instead.

Any clue?

Cheers,
Olivier


2022-02-09 11:17:05

Mithil

Thank you so much. This list helped me a lot and saved me a lot of headache.


2021-10-09 11:41:39

Pedro Oliveira

Helo, Peter!
Thank you for your answer.
I don't know how to create UDFs and have no sufficient knowledge to evaluate your suggestion, although I believe it works.
I solved the problem with the creation of a simple auxiliary table with the correspondence between the months in English and Portuguese, which I then referenced with a combination of an INDEX-MATCH to feed the REPLACE function to switch from "Aug 2021" to "Ago 2021". From this point onward it was easy to transform the text string "Ago 2021" into a date with DATEVALUE, and then feed the result to the EOMONTH function.
Thank you for your time and your interest in this subject.


2021-10-08 08:57:56

Peter Atherton

Pedro, try again!

Convert Text A1, type =SPLITTXT2DATE(A1)
Convert Text A1 to EOMONTH type =EOMONTH(SPLITTXT2DATE(A2),0)

{[fig}]

Regards
Peter


2021-10-08 06:44:03

Peter Atherton

Pedro, Try this UDF

Function SPLITTXT2DATE(rg As Range) As Date
Dim x, i As Long, j As Long, c As Range, _
d As Long, m As Variant, yr As Long, y As Variant
Dim mnth()
mnth = Array("Jan", "Feb", "Mar", "Apl", "May", "Jun", "Jul", "Ago", "Sep", "Oct", "Nov", "Dec")
x = Split(rg, " ")
On Error Resume Next
If UBound(x) = 2 Then
d = x(0)
m = x(1)
yr = x(2)
ElseIf UBound(x) = 1 Then
d = 1
m = x(0)
yr = x(1)
End If
For j = LBound(mnth) To UBound(mnth)
If mnth(j) Like Left(m, 3) Then
m = j + 1
End If
Next j
SPLITTXT2DATE = DateSerial(yr, m, d)

End Function

Change the date in months array to Aug within the quotes

[{fig}]


2021-10-07 08:42:52

Pedro Oliveira

I've been looking for something of this sort but somehow this solution doesn't work on my Excel 2013. My PC is configured with Portuguese regional settings (date formats, currency, decimal point, etc...) but I use all of my Office 2013 programs (including Excel) in English, because functions, menus and commands are a lot more easy to understand and memorize.
I have a text string in one cell from which I want to extract the month year information, so I can find the last day of the corresponding month, to calculate the daily average of a given metric for that month.
The problem with this strategy is that the function EOMONTH only accepts numeric values for its arguments: EOMONTH(start_date;months). I tried using the TEXT function, like it's here described with [$-0816] but the problem is that this function doesn't recognizes "Aug 2021" as a date, because in Portuguese "August" ("Aug") spells like "Agosto" ("Ago"). So, I can extract the month year part of the text string with no problem, but when I try to pass "Aug 2021" to the TEXT function and transform it to "Ago 2021", either to Excel immediately recognize it as a date value in the mmm yyyy format or to try and pass it to DATEVALUE, it doesn't work, and therefore I cannot use the EOMONTH function unless I can solve this problem. Any suggestions?


2021-06-01 13:18:57

Mert

wow What a tip this was what I looking for thanks a lot


2021-02-23 12:54:22

Joe

Thank you so much. It helped me a lot :)


2021-01-06 09:38:56

flopps

Hi, this article is a big help but i still have some problems. when you change the region format from windows 10 to something else the lcid is not changing. so you have to get the country code which is completly different to the lcid format. for me as a german the lcid is 1031 and the country code is 49. is there a internal mapping i can use or do i have to implement my own?

Greetings :)


2020-04-28 17:58:12

Igor

excelent! thanks.


2020-02-21 09:49:12

Hubert

thankyo brother


2020-01-17 10:25:10

Gehad Alahdal

Hi Dear Allen Wyatt,

I have gone through your VBA code, could you please help me to have the code of changing the cursor from lite writing to right writing, i have an Arabic computer when i write an Arabic it is from right to lift whereas the English are from lift to right, to use my computer when i write Arabic i use ( Alt + Shift ) it changes the cursor from lift to right so i write Arabic when i want to change to English i use the same method ( Alt + Shift ) it change to the lift and then i write English so, i need a VBA code to this matching ( Alt + Shift ) for some cells in my excel.

More explanation, am writing Arabic in my most cells i need when i come to a specific cell it changes itself to English instead of i use ( Alt + Shift ) example.
cells A1 , B2, D5 and E2 in Arabic but when i come to cell G5 it change the cursor to English itself without using (Alt + Shift ).

I think it is clear for you now, thanking you looking to have your help in this VBA code (Alt +Shift ) .


2019-11-20 20:58:38

Peter Atherton

Nigel Warfield
I only came across this today; I suppose you have already done this but for interest I tried this UDF

Function SplitText(ref, n, direction) As String
'ref is the text or range _
n is the number of words to split _
direction is L for left and r for split on right

Dim L As Integer, pos As Integer, i As Integer

If LCase(direction) = "r" Then
Select Case n
Case Is = 1
L = InStrRev(ref, " ") - 1
Case Is > 1
For i = 1 To n
If i = 1 Then
pos = InStrRev(ref, " ") - 1
ElseIf i < n Then
pos = InStrRev(ref, " ", pos - 1)
Else
L = InStrRev(ref, " ", pos - 1)
End If
Next
End Select
SplitText = Trim(Right(ref, Len(ref) - L))

ElseIf LCase(direction) = "l" Then
Select Case n
Case Is = 1
L = InStr(ref, " ") - 1
Case Is > 1
For i = 1 To n
If i = 1 Then
pos = InStr(ref, " ") + 1
Else
L = InStr(pos, ref, " ") - 1
End If
Next
End Select
SplitText = Trim(Left(ref, L))
End If

End Function

(see Figure 1 below)

Figure 1. Split Text Funfuction


2019-11-19 06:45:25

Urban Freij

I just set "[$-0409]DD-MMM" in Format Cells > Number > Custom. It works swell in Excel 2013.


2019-05-21 06:18:01

Martin Glenn

Doesn't work in Excel 2016


2019-04-18 03:14:46

Mochammad Firmansyah Arief

did good sir.... thanks


2019-04-15 10:51:21

Nigel Warfield

Hi I am a subscriber but haven't managed to find a suitable function to separate out a cell that contains a foreign language from the English translation. I want to leave the Greek word in the first cell or vertical column and have next to it in a separate row and cell the translation.
By example I have copied a little of language vocabulary list and I get the following example:
τα έπιπλα furniture
το εργαστή laboratory
το ισόγειο ground floor
μοντέρνος modern
ξεχωριστός separate
όπου where in which
κάθε each every
κάθομαι I stay live
As you can see the first letters form a greek word (taking the 1st row or cell entry is τα έπιπλα laboratory . I could do this manually but that would mean introducing a delimiter such as comma, colon or some such symbol and use the data transpose in Excel but if you have a whole bunch of these to do it takes a long time and I am trying to insert them split columns. I have seen a Trim function but that was a step to far and then there might be a way to use find and replace but again the syntax is confusing form and appreciate if you could assist please.


2019-03-29 05:33:39

Marcus Asssis

Hey, I think I've found another way to fix this when more than one region uses the same excel file.

=TEXT(TODAY(),IFERROR(IF(VALUE(TEXT(TODAY(),"AAAA"))=YEAR(TODAY()),"MM-AAAA"),IFERROR(IF(VALUE(TEXT(TODAY(),"JJJJ"))=YEAR(TODAY()),"MM-JJJJ"),IFERROR(IF(VALUE(TEXT(TODAY(),"YYYY"))=YEAR(TODAY()),"MM-YYYY"),"YEAR FORMAT NOT RECOGNIZED"))))

It should return "03-2019" for English, German, and most latin languages (Spanish, Portuguese..etc)

Feel free to give me any suggestions or to add other Year language formats, I only know these 3

Cheers!


2018-02-07 05:07:50

Mohamed

Thank you so much< that was very helpful


2018-01-04 06:38:22

Harvir Singh

Dear sir/mam
I has asked one question. so please miss coll at this num.07017816694.
thank you.


2017-11-08 11:51:53

Willy Vanhaelen

@Don
TEXT is an Excel function. Use the vba FORMAT function instead as in Range ("A1")=Format(now,"yyyy-mm-dd"). A1 must be fomatted as text first.


2017-11-07 10:03:23

Don

Hi Allen,

Good article. Thinking globally, I like the example to avoid problems. I'm looking for format a date as text, but in a VBA formula. Because of that, the A1 confuses me (=TEXT(A1,"[$-409]mmmm, yyyy")).

Is there a way to have a formula like "Date1Text = Text(Date1, "yyyy-mm-dd")"
Date1 is the current date and I'm looking to create a version of the date to store it as text (for export reasons, the format of yyyy-mm-dd is requested).


2017-09-04 03:27:55

Hi

Thanks, this was easy to find via google and quickly helpfull


2017-08-29 05:11:56

Hadi

Thanks for this sharing.

Sorry to answer here for those using the french version of MS Excel. This is how I cope with their issue:
1. I create a named formula:
Name: xl_FR (for ex.)
Refers to: = Texte(Aujourdhui(); "d") = "d"
Click OK.
2. in any of your formulae, you can check if xl_FR is true/false and use "jj" or "dd" accordingly.

Hope this helps
Hadi
Tip: You can even create in your workbook named variables dd = si(xl_FR;"jj";"dd") etc... and use them in your Texte function. Similar technique can be used with Cellule (Cell in english) function


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.