How to translate Excel formulas locally

Posted on domingo, marzo 03, 2013 by Pedro Wave

Excel formulas translation

When I search formulas in the blogs of Excel gurus, all are written in English and I can not copy and paste them into my spreadsheets. Sure that ever happened to anyone reading this. Another problem of the same kind occurs when I try to write in English Forum a formula I have written in Spanish language.


In this link you can download the latest version of the translator, by pressing the Download button in the menu at the top of my SkyDrive:

In Holland, the excellent MVP Ron de Bruin wrote an article explaining internationalization how to determine the version and the local language of Excel and geolocated features to consider: rondebruin - international

One of the pages with Excel formulas to learn more is Chandoo.org written in English by the CEO - Chief Excel Officer, Purna Duggirala, whose purpose is to become us formidable Excel users. And it is getting with its excellent items! like this: chandoo.org - formula forensics no 033

Of course I see the formula written in English does not help much and can not enter in a cell to see what happens, for example:

What happens is that you receive an error message when you enter the formula in a version of Excel in Spanish and are not readily available by translating manually each change of its functions and replacing the commas by semicolons, where applicable, to obtain this formula translated into Spanish:

The reverse case is to have the formula written in Spanish and want to translate into English to upload it to an international forum. The forward and backward translation of the formulas will be resolved by my Excel formula translator that you can download for free from this page if you read a little further down.

Tables to translate Excel functions

Do not try to use a table to translate Excel functions from one language to another, such that there are many tables on the Internet, with the added difficulty that each new version of Excel generates function names with their translations to various languages, eg:

This translation process is very laborious and an example of bad productivity, being prone to error (error-prone) and being difficult to verify these bugs (bug-prone), as translated functions not match with the original, operators can be misinterpreted and the arguments passed may be incorrect, which is difficult to correct and very slow to get the exact translation of the formula.

Excel formulas translators

To solve the translation of formulas are suggested several procedures, such as those proposed by George L. Dunkelman in his blog: jldexcelsp - traduccion de funciones excel

I don't forget to mention at David McRitchie who has explained on his website several methods to translate formulas from English into other languages, especially with the use of the Immediate window in the VBE Macro Editor: dmcritchie.mvps.org - excel/language

Formula Translation in English or the Excel local language

I loaded an early version of this translator into the AyudaExcel.com two years ago to share it with my friends on the forum: ayudaexcel.com - traducir formulas al ingles al idioma local excel

In this link you can download the latest version of the translator, by pressing the Download button in the menu at the top of my SkyDrive:

In column A write the apostrophe character (') followed by the formula in English. Write formulas in Spanish directly. (Enter formulas in cells marked in yellow).

Each file columns indicate the following:
A column  Original formula
B column Spanish formula
C column English formula
D column Result formula

In cell A2 I have included the English formula copied from the Chandoo.org page, translated into Spanish appears in cell B2. In cell D2 shows the result of applying the formula, not being correct: #N/A. If you enter a formula in column A in English, will be translated into Spanish but not interpreted correctly, indicating the error: #NAME? (See A7 cell).

The list separator character for Excel function arguments in English is the comma (",") and in Spanish is the semicolon (";"). This separator is not set from Excel options but from the Control Panel of the operating system, entering the Regional and Language Settings, Additional Settings, Custom number formats, and modifying the list separator.

The formula translator is based on two properties of Excel VBA:

The macro code to translate formulas is in the worksheet "Formula":


Office Language Interface Pack (LIP)

I do wish to comment on a method to translate formulas allowing translation between two languages ​​without being one of them English. The downside is that you have to be constantly switching from one language to another to see the translation of formulas.

Going into Excel Options and selecting the language, you can see the languages ​​installed in Office and choose the languages of the Help and the user interface, if it has been added.


To download additional languages, adds new languages ​​from Excel options or visit these pages:
office.microsoft.com - office language interface pack lip downloads
office.microsoft.com - do i need a language pack or language interface pack

To learn more about the location of Excel formulas and functions you can read these articles:
pedrowave.blogspot.com - languages translator in excel


1 Response to "How to translate Excel formulas locally"

.
gravatar
Unknown Says....

Hello, My name is Juan Macedo, I have some problems with Spanish Excel functions, that could be interesting for You:
I found these in Office different versions (last in Office 2013)
The Excel Address function is translated to DIRECCION, the Excel Offset function is translated to DESREF, well in Excell worksheet is possible to use the spanish function, but in VBA it is impossible to use any of both (English or Spanish versions), for some of the functions it is possible to use the english version when the spanish function is not available, but at least the two mentioned do not work.

Thanks

Juan Macedo

Leave A Reply

Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.

Tell me if you like what you read here and if you don't like, tell me why. I've enabled comment moderation. Your comment will be published ASAP.

Mi Lista de Blogs- My Blog List