Excel Unplugged

Learn a foreign language with Excel

Surprisingly, this is not a post about the Translate feature in Excel. Rather we will take an in depth look into Cell Format and a great feature of Excel that allows you to change the names of days and months into any language you prefer, by using either the Cell Format or the TEXT function in Excel.

Note: If you are a visual person or would rather learn by video, you can find a video version of this post here or view it at the end of this post.

Here is what we are talking about…

First, we will need a date

Custom Format and TEXT function and LCID

Now one thing that you can do with this date is go to Cell Format either by Ctrl + 1 (one on the alphanumeric part of the keyboard) or by Right Clicking on the cell and selecting Format Cells.

Custom Format and TEXT function and LCID

Then in the Format Cells dialog box you choose Custom and in the Type field you write dddd. That will format the cell to show only the name of the day on which the date occurred.

Custom Format and TEXT function and LCID

However, here is something else you can do. In the Type field, you can write dddd [$-40C]…

Custom Format and TEXT function and LCID

…and right away, we are learning French. Let us explain what is behind this. The format is generally this

dddd [$-LCID Hex Code]

Now the LCID Hex Code is the trick that points to a language you want to display the names of the days in. LCID are so called Locale IDs Assigned by Microsoft which you can find at

https://msdn.microsoft.com/en-us/goglobal/bb964664.aspx

Custom Format and TEXT function and LCID

First, you look up the language you prefer in column one and then search for the LCID Hex Code of that language in the second column. And with this you can let your imagination run wild.

Custom Format and TEXT function and LCID

But keep in mind, this can also be very useful. If you only format a cell with dddd, what you get is the name of the day that will vary from Excel to Excel. Depending on the regional settings of the host operating system but by using the dddd [$-LCID Hex Code] format, you set the language for all viewers, regardless of the regional settings of their operating systems.

Two other great things come from this, if you replace the dddd with mmmm, or use them together, you can learn the month names in the desired language and both (dddd and mmmm) can be used with one of the greatest Excel functions called TEXT.

Custom Format and TEXT function and LCID

Here is a video version of this post that goes into more detail.