Excel functions with text value arguments - Second part

In the previous article we described how you can avoid errors when using CELL and INFO functions with text arguments in non-English Excel. When you open your file in English Excel, it is trivial that function names will be translated. The problem is that the text argument will not be - so we suggested to use the English text arguments in your local Excel.

Unfortunately there are some cases where this solution does not work... in these cases you can see error when you use the formula on a computer with non-English Regional setting.

Date formatting code problem

The first one is TEXT function where the second parameter is format_text. In Italy it looks like this:

= TESTO (A1;"gggg")   →   “Mercoledi”

In A1 you have a date. “gggg” stands for the date format long day name (because in italian day = giorno), so the result is for example “Mercoledi” (“Wednesday”).

Unfortunately, if the file is opened by a colleague in his let’s say English computer the result of the formula will be “gggg” - because in English the symbol of day is “d”, and the formula takes the “g”-s literally!


= TEXT (A1, "gggg")   →   “gggg”

In English only this formula will work correctly:


= TEXT (A1, "dddd")   →   “Wednesday”
But in this case on an Italian computer you will see “dddd”...

Why it is not possible to use the English “dddd” similarly as in CELL and INFO? Because this day format code comes from the computer’s regional settings, not from Excel’s language setting! (Under Windows 7 you find it: Control Panel / Region and language)


When regional setting is Italy, “gggg” works correctly:
 
Note: In this picture you see that Italian Windows 7 uses English date formatting codes in Control Center. The Italian Excel is not aware of this Windows formatting notation, it uses it’s own.

When regional setting is Italy, the English “dddd” will not work:
 

In Hungary you should use “nnnn”:
 
Note: In Hungarian Windows 7 and in Hungarian Excel the formatting codes are the same.

You can always see the correct format codes in Excel under Format Cells / Custom:




Indirect reference problem

A similar problem occurs when you use the R1C1 references in INDIRECT function.
For example, this formula refers to the cell A1:


= INDIRECT ("R1C1", 0)

Italian is a fortunate language, these letters are the same as in English (Riga and Colonna stands for Row and Column), so the formula will work.

But if the file is opened by a Hungarian colleague the formula returns a #REF error!

In a Hungarian computer you need to write:


= INDIREKT ("S1O1", 0)
(Sor and Oszlop stands for Row and Column).

Solution of the problems

Sometimes the solution can be very simple, you can avoid this kind of problems if you do not use the TEXT function for dates. For example instead of TEXT with “dd” you can use the DAY function. But there is a general method you can use for all of the cases.


So I would like to cite an old article by Ivano Chiappa of
RIO: Supplementary Information Functions .
You can download his file in which five Microsoft Excel 4.0 macro functions are described. It is a very powerful document you will find advantageous in your professional life.

Important! Ivano says in his article
: "These functions can not be inserted directly into the worksheet, however, you can use them in a defined name" .
I should add that if you use this methods in an Excel  2007 or 2010 workbook,  the file must be saved with extension .xlsm (as Excel Macro-Enabled Workbook, even if no VBA code is used), or as a file in binary format: .xlsb.


The following 5 functions are described in the Ivano’s file:

in Italian: in English:
INFO.CARTELLA.DI.LAVORO GET.WORKBOOK
INFO.CELLA GET.CELL
INFO.DOC GET.DOCUMENT
INFO.FINESTRA GET.WINDOW
INFO.AREA.DI.LAVORO GET.WORKSPACE

In our case we will use INFO.AREA.DI.LAVORO to solve the above mentioned text parameter problems. Using argument 37 this function returns an
array containing information about the current international and country settings. (The equivalent in VBA is the property Application.International)
From the array we can select the information with index function - just need to know the index numbers. For the date formats these are: 19, 20, 21.


INFO.AREA.DI.LAVORO(37) date format parameters and result:

Formula in Italian in English in Hungarian
Year INDEX (INFO.AREA.DI.LAVORO(37); 19)

“a”

“y”

“é”

Month INDEX (INFO.AREA.DI.LAVORO(37); 20)

“m”

“m”

“h”

Day INDEX (INFO.AREA.DI.LAVORO(37); 21)

“g”

“d”

“n”



Let’s see how can we use it in Excel:

First define a named formula which will give us the day symbol of your system:
day_simbol = INDEX (INFO.AREA.DI.LAVORO(37); 21)

In Italian the result will be: day_simbol = “g”


in the English version it will be:

day_simbol = INDEX (GET.WORKSPACE(37), 21)

and the result is: day_simbol = “d”


Second, modify the original formul
a: =TESTO(A1;"gggg") ...nesting the defined name. If you want to have the long day name, you need “gggg” so the defined name should be nested into a RIPETI (REPT) function to have 4 letters:
= TESTO (A1; RIPETI (day_simbol; 4))

in English:

= TEXT (A1, REPT (day_simbol, 4))

day_simbol returns "g" if the settings are set on the Italian language and "d" if they are set to English and vice versa. It will return the correct symbol in any environment, for any language - so you can be sure your new formula will work in international environment!



We can solve the
problem of “R1C1” similarly. The necessary index numbers are 6 and 7:
INDEX (INFO.AREA.DI.LAVORO (37), 6) = row symbol “R” (hungarian: “S”)
INDEX (INFO.AREA.DI.LAVORO (37), 7) = column symbol “C” (hungarian: “O”)

So the named formulas look like:


row_simbol = INDEX (INFO.AREA.DI.LAVORO (37), 6)
column_simbol = INDEX (INFO.AREA.DI.LAVORO (37), 7)

Modify the indirect formula:

= INDIRECT ( row_simbol & "1" & column_simbol "1")

Example file

Based on Ivano’s article and his file I prepared a sample file with an example in which two matrixes are created containing the names of the days and months. You can determine the length of the day and month name (number or name) and you can choose which day and month you want to see first returned from the matrix.



This file will work in every environment - look into it and do not hesitate to ask if something is not clear!


(English translation: Kris + Gabor)

Comments