How to translate Excel formulas locally

Posted on marzo 03, 2013 by Excel 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:

TraductorFormulasPW1.xls

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:

cherbe.free - traduc fonctions xl97

excelavanzado.com - glosario traducción de funciones

xperimentos.com - tabla de traduccion de formulas excel en espanol a ingles

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 Google Drive:

TraductorFormulasPW1.xls

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:

FormulaLocal: explained in msdn.microsoft.com - FormulaLocal
Formula: explained in msdn.microsoft.com - Formula

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

Private Sub Worksheet_Change(ByVal Target As Range)
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Escribe en las celdas de la columna A un signo de apóstrofo (') seguido
' por la fórmula en inglés, con formato de macros VBA.
' Escribe directamente las fórumlas en el idioma local (por ejemplo: en español)
'
    Dim FormulaLocal As String
    Dim FormulaEnglish As String
    
    ' Columna A
    If Target.Column = 1 And Target.Row > 1 Then
        ' Continua con error
        On Error Resume Next
        ' Copia formula en columna D
        Target.Offset(0, 3).Formula = Target.Formula
        ' Error en la formula
        If Err.Number <> 0 Then
            Err.Clear
            ' Copia formula local en columna D
            Target.Offset(0, 3).FormulaLocal = Target.Formula
        End If
        If Err.Number <> 0 Then
            ' Error en la formula
            Err.Clear
            FormulaLocal = ""
            FormulaEnglish = ""
        Else
            ' Copia formula local (en español)
            FormulaLocal = "'" & Target.Offset(0, 3).FormulaLocal
            ' Copia formula en inglés
            FormulaEnglish = "'" & Target.Offset(0, 3).Formula
        End If
        ' Formula local en columna B
        Target.Offset(0, 1).Formula = FormulaLocal
        ' Formula en inglés en columna C
        Target.Offset(0, 2).Formula = FormulaEnglish
    End If
End Sub

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 - how to localize applications

pedrowave.blogspot.com - languages translator in excel

1 Response to "How to translate Excel formulas locally"

Unknown Says....

15 diciembre, 2013 05:02

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

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.

#ExcelPedroWave

Traducir el blog