Month Name in regional language and wanted in English

Question

I use this formula to get the first three letters of the month with first letter in capital (i.e. Jan, Feb, Mar, Apr, ...) :

=UPPER(LEFT(TEXT(B1;"mmm");1)) & MID(TEXT(B1;"mmm");2;2)

But it give the result to me in my own language and I want it in English.

My Excel and all Office are in English, I just have French regional settings (that I prefer to keep, or I don't really want to switch them every time I use that kind of function).

Is there any way to change the language of the output of TEXT function?

Answer 1

You can force TEXT to use a specific locale (English US in your case...).
Try this: =UPPER(LEFT(TEXT(B1;"[$-409]mmm");1)) & MID(TEXT(B1;"[$-409]mmm");2;2)

Answer 2

Building on @Amit great response, I would say: =TEXT(B1;"[$-409]mmm").

As the English name of Month are already capitalized on the first letter.

If it's not enough, there is the Proper function that will only capitalize the first letter of the word.

---

format mmm already is 3 char long in English. No need for LEFT(). Proper() capitalizes the first char only.
Note that using 040C for French for example, it might return longer strings ! E.g: "Juil." for July.