Specifying a Language for the TEXT Function

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.

Thanks a lot! Really helpful!

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

@Olivier
See Settings > Time & Language > Region.

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

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

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.

Pedro, try again!

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

{[fig}]

Regards
Peter

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}]

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?

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

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

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 :)

excelent! thanks.

thankyo brother

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 ) .

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)

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

Doesn't work in Excel 2016

did good sir.... thanks

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.

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!

Thank you so much< that was very helpful

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

@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.

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).

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

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

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.

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

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

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

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.

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

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

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?

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!

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