PDA

View Full Version : Online Excel Formula Translator to other languages



maninweb
2011-08-23, 02:27 PM
Hi there...

I have published my first version of my free online tool at http://translator.excel-ticker.de (http://translator.excel-ticker.de/index.php?Language=en) for translating single and especially combined Excel 2010 (or lower) Formulas to another language. Currently the tool supports English, German and French.

The main reason for me to implement the tool in PHP was, that I am also active in English speaking forums and when trying out posted formulas, in many cases, I have to firstly change my Excel language, inserting the formula in a sheet, save it and then switch back to German. I thought, it's a complicated way for just one formula, especially if the formula is more complex and retyping it, is some 'work'. Also, I experienced sometimes that a user has, for example, only a french version of Excel and wants to post a formula to a german speaking forum and a translation of the formula is not possible on the fly.

I am currently adding more languages to the tool. Then I am planning to create a widget or something similar for use and/or embed in forums or blogs.

But before realize my plans, I would be thankful, if you can give me a feedback to the ideas and tell me if such a tool can be useful for you. Thanks in advance.

Regards :-)

Bob Phillips
2011-08-23, 03:12 PM
I think it would be better if the input and output boxes were separate, so you can still see your original formula. You could then switch target from say Deutsch to Francais without changing the target language, which would be better.

Bob Phillips
2011-08-23, 03:13 PM
Also, in the English text, your eaxmples should be delimited by comma not semi-colons.

maninweb
2011-08-23, 04:50 PM
Hi Bob...

much thanks for your suggestions and feedback :-)

Yes, I think, you are right, may be better to have 2 boxes for the formulas. Will be implemented.

Regards :-)

Ken Puls
2011-08-23, 05:58 PM
Yes, those commas are an issue for those of us in North America. (Not sure where else may suffer that.) When I translate the following:
=ROUND(MIN((SUM($F54:G54)-$C$10*COUNTA($F9:G9)/12)*$C$12*$C$13,$C$14)-SUM($E56:F56),2)

I get this:
=RUNDEN(MIN((SUMME($F54:G54)-$C$10*ANZAHL2($F9:G9)/12)*$C$12*$C$13,$C$14)-SUMME($E56:F56),2)

I'm guessing that it should be:
=RUNDEN(MIN((SUMME($F54:G54)-$C$10*ANZAHL2($F9:G9)/12)*$C$12*$C$13;$C$14)-SUMME($E56:F56);2)

Awesome tool though... I can see where that would be very helpful.

(Also agree with Bob's suggestion about two windows.)

Bob Phillips
2011-08-23, 06:50 PM
Not necessarily Ken. I don't think you have semi-colons JUST because it is German or French, it is more to do with the international settings (of course, the international settings for France and Germany would default to semi-colons, but can be changed). Having said that though, I think it makes sense to change to the expected defaults, but I guess that makes it that much harder.

Bob Phillips
2011-08-23, 06:54 PM
What about months that are spelt out, such as

=SUMPRODUCT(--(TEXT(A1:A20="mmmm")="February"))

=SUMPRODUCT(--(TEXT(A1:A20="mmm")="Feb"))

And whilst we are at it, what about

=TEXT(A2,"dd")

In French that would be

=TEXTE(A1,"jj")

and in German

=TEXT(A1,"tt")

Ken Puls
2011-08-23, 06:55 PM
Fair enough. My experience is pretty limited with foreign language translation in Excel. I guess the people I've helped have either been able to work with what I've given, or translated it on their own. :)

Bob Phillips
2011-08-23, 07:07 PM
You mean that you haven't read that chapter in PED?

maninweb
2011-08-23, 07:08 PM
Hi Ken, Hi Bob...

Ken, much thanks your feedback too :-)

Yes, the tool does not currently convert the commas to semi-colons and as Bob said, it depends on the system's settings. I changed my Excel language to EN and still have semicolons on my Windows 7 DE system. And I tried the same on my Windows 7 VM in English to change the Excel language to German and the have the commas instead of semicolons (=WENN(B1<>1,"Hello World","Ok"))

I think, Bobs suggestion to set it per default would be a way and will surely apply to most systems but not to all. I can create an option, setted by default, and let the user decide by himself. The problem when converting the commas to semicolons will be to not replace the commas included in static text. PHP however offers powerful functions to find regular expressions; however I must teach myself a little bit in RegExpr..

So, my next steps will be adding more languages, primarly languages with a latin charset. Then change the formula boxes as suggested by Bob and try some replacements for the commas and semicolons. And when I am ready, I'll post here an update.

Update: I have been writing while yu have posted. Months who are spelt out can be replaced; concerning the formats, hmm, I must think about it. But it's on my list now :-)

Regards :-)

Ken Puls
2011-08-23, 07:36 PM
You mean that you haven't read that chapter in PED?

Nope. Never had a need to. ;)


...when I am ready, I'll post here an update.


Cool deal Mourad, looking forward to seeing the next iteration. :)

jpr73
2011-08-24, 11:06 AM
Hay,
Good idea Mourad, I will appreciate your online formula translator
What about the following functions (in French syntax) :
INFORMATIONS(no_type) :
=INFORMATIONS("répertoire") =INFORMATIONS("nbfich") =INFORMATIONS("cellule") =INFORMATIONS("versionse")
=INFORMATIONS("recalcul") =INFORMATIONS("version") =INFORMATIONS("systexpl")

CELLULE(type_info;[référence]) :
=CELLULE("adresse";$B$3) =CELLULE("colonne";$B$3) =CELLULE("contenu";$B$3) =CELLULE("coord";$B$3)
=CELLULE("couleur";$B$3) =CELLULE("format";$B$3) =CELLULE("largeur";$B$3) =CELLULE("ligne";$B$3)
=CELLULE("nomfichier";$B$3) =CELLULE("parentheses";$B$3) =CELLULE("protege";$B$3) =CELLULE("type";$B$3)
Best regards

maninweb
2011-08-24, 08:39 PM
Hi jpr73...

Many Thanks for your feedback; I will check these functions and if necessary correct/add them :-)

Regards :-)

Tony Jollans
2011-08-26, 09:05 AM
Excellent Idea. Microsoft has such a long way to go on languages and anything that helps is worthwhile.

A couple of points:

It seems to require function names to be in upper case.

Comma/semicolon separators are complex, but Excel let's you override the system setting and change things without quitting Excel, so nobody need have a problem. I would stick to simple and use semicolons for non-English European languages and commas otherwise.

Are you aware of http://www.piuha.fi/excel-function-name-translation/index.php

Bob Phillips
2011-08-26, 10:48 AM
Seeing Tony join in reminds me that he posted a nice MS 'feature' recently, where the MID function in Swedish is EXTEXT in Excel 2003 and MITT in Excel 2010 (there may be others). Ron de Bruin also mentioned that in 2007 there was this bug that is fixed in SP2 http://www.rondebruin.nl/atp.htm (http://www.rondebruin.nl/atp.htm).

Not simple is it?

maninweb
2011-08-26, 01:05 PM
Hi Tony, Hi Bob...

Thank you, Tony, for your feedback. Yes, the tool assumes the function names in upper case letters, because Excel transforms
formulas to upper case after enter was pressed.

Bob, this means, I have to add those special cases to the array; assuming the formula names are in all cases unique. If so, then
I think, this problem will be resolvable.

Thanks both for the links :)

I am currently preparing and extending my Excel file I used for the three current languages (I use a macro for generating the php files
with the arrays) for more languages. I will then upload the file here; hoping that some people will have a look on it.

I bought www.excel-translator.de (http://www.excel-translator.de) (and .com) yesterday. The new domains are however only redirecting to the previous domain
as I am also moving to another provider, but that's another problem and a long story.

A subscript is now pointing to a developer version of the translator: http://translator.excel-ticker.de/developer.php Currently a copy
of the released version is stored, but this will change soon. The developer version will then reflect my last developing state and when
it's tested move to the release version. And I am also creating a support and bug reporting site for the translator; this work is in progress.

Best regards :-)

maninweb
2011-08-27, 10:51 AM
Hi all...

I have uploaded my basic file serving me for the translations. The Excel file includes the translations
of the english functions of Excel 2010 for German, French, Italian, Spanish, Dutch, Portuguese and
Swedish. The worksheet is protected by an empty password.

The translations were done by Excel, I installed all language packs on a VM, switched to another
language and pressed my button "Extract". The first step was to enter all excel formulas in English
manually; hope I have not forgotten one.

When opening the file, your current language ID is shown in the red field on top left. If you want
for example support me by providing languages, please just fill in in a empty column your language
ID (a number like 1033), a language name and code (just as you like). When pressing "Extract"
a dialogue searches those ID and puts all formulas to the column. If you are using the file,
please don't change the sorting (based on English). The sheet contain hidden row, in case I
forgot formulas for a character (A-Z); each char has placeholders for max 64 functions.

I am now going to add the new languages to the translator and performing some changes to
the algo; some questions:

- What do you think, is there a need to also translate older Excel 4 macro functions?
- May it make sense to translate the most used Add-In functions and if so which ones?

Best Regards :-)

Ken Puls
2011-08-28, 05:24 AM
Wow... a lot of work!

Personally, I don't use much (if any) of the Excel 4 macro functions. I know there's some things that they're very useful for, but since they trigger security warnings in Excel 2010, and I never really did learn them, I avoid them.

Re Add-ins... are you talking about add-ins like Laurant Longre's MoreFunc addin? I personally avoid them too, but I've seen quite a few people recommend that one at mrexcel.com...

maninweb
2011-08-28, 05:57 PM
Hi together...

I have released version 1.05 on my server and added following features...


New languages for translations, so that the translator knows about the formulas for English, German, French, Spanish, Italian, Dutch, Portuguese and Swedish.
UTF 8 support for the language files for the Excel formulas and interface translations (currently only German, English and French). May be great if someone who speaks more languages can support me.
Parameter for setting the interface language when calling the translator, example http://translator.excel-ticker.de/translator.php?Language=en loads the english interface, de and fr will then load German and French; default is English.
Two textboxes are now shown for the formulas, the first for the source formula and the second for the translated formula.
Minor changes for the parser. However, as I am planning to also be able to translate keywords in formulas (CELL("Filename"), ...) and some of the date and time formats, I will rewrite the parser as the current version is not able to do that.
The developer version for the translator is ready and and has additional options; the parser can be choosen there, primarly for me. So when developing, I am completely independent from the released version.

Unfortunately, the file for extracting the formulas had a minor bug (especially for FALSE and TRUE), I have fixed this and uploaded a new version here.

@Ken: thanks :) Personally I also do not use these old macros, but I know few people who do. May be perhaps the best to firstly finish the translator with the current formulas and the consider the Excel 4 macro formulas later when all things work fine. Concerning the Add-Ins, I thought about the Analysis Toolpack, for example for Excel 2003. As I know and if I am right, a considerable number of people is using this Add-In; for example for calculating the week number with the ISO Standard (I also avoid this Add-In). However, I am not sure if a localized version of this Add-In is installed with a language pack. If not, it will be IMHO more difficult to retrieve the translated formulas.

Next steps for the translator will be to replace and improve the parser module, support the replacement for commas and semicolons, make the layout a little bit nicier, add languages, etc.. However this work will only start from September, as I am busy next week.

Best Regards :-)

Ken Puls
2011-08-28, 09:18 PM
Analysis toolpack, definitely. I checked your file though, and you listed the most common two that I use: EOMONTH and MROUND.

maninweb
2011-08-29, 10:00 AM
Hi Ken...

yes, I also have seen this that some functions of the Analysis toolpack seems to be now integrated in Excel (2010) itself. So, I think, I must firstly get all formulas included in the toolpack.

Best Regards :-)

maninweb
2011-08-30, 07:46 PM
Hi there...

I have released Version 1.25 Beta of the translator (http://translator.excel-ticker.de). Following features are new or improved...


A rewritten formula parser, which is now, IMHO, better prepared to find keywords in some Excel functions for translating them in later versions of the translator.
The translation is being verified by retranslating the result formula to the original language. If this result is then not the same, then something went wrong. A status message is displayed.
Quoted text and sheet names in formulas may now be preserved.
Spaces before the ( in formulas or in other places of the string may be preserved and correctly recognized.
The parser can translate formulas in upper and lower cases. However this currently only works for non specials characters (like ÄÜÖ) and is due to an UTF problem on my server; don't know why.
An option for choosing to replace semicolons by commas and vice-versa has been added.
The languages displayed (source and target) are new sorted alphabetically depending on the interface language.
The buttons for the interface languages moved to the left for better adding more languages later.
...

Feedback is well appreciated, thanks and for me, it's time to test many many formulas :-)

Best Regards :-)

jpr73
2011-08-30, 09:47 PM
Hay Maninweb,
Great job
I propose some correction about French translation :
- Excel Formula Translator
"Insérez une seule ou une combinaison valide de formules de Microsoft Excel dans le champ de formule source, puis sélectionnez la langue source et la langue cible et définissez éventuellement plus d'options. Les traductions commencent après un clic sur « Traduire la formule » et le résultat s’affiche dans le champ de formule cible."

- Source and Target Language : "Suédois"
- Options : No action = "N'effectuer aucune action"

- I also test the Cell and the Info formula : the [type] is not translated but I am sure you will take care later

Best regards

maninweb
2011-08-31, 09:19 AM
Hi jpr73...

Un grand merci pour tes corrections :)

Thanks for your corrections, I have changed the strings. The keywords in formulas (like CELL) are currently not translated. I would like to firstly ensure that combinations of formulas are correctly translated, then the keyword translations will be done in a later version and may be, I think, a little bit tricky.

Best Regards :-)

maninweb
2011-09-01, 08:18 PM
Hi there...

I have added new languages, 16 languages are now supported and the translator may now also translate formulas in lower cases. PS: the reason you will find Japanese in the translator is that one formula differs from the English ones (DOLLAR = YEN).

I also updated my Excel File with the translations. As this file unfortunately exceeds the allowed size here, I uploaded it on my server; you can find the file here http://translator.excel-ticker.de/documents/formulas.xlsm. The file contains 19 languages, but in Hebrew and Korean, the formulas are the same as in English. The Excel file also now includes a sheet for comparing formulas between 2 languages.

Best Regards :-)

maninweb
2011-09-10, 06:50 PM
Hi all...

I have updated the formula translator to Version 1.40 Build 100911. New features and updates...



The translator has now also its own fanpage at Facebook (http://www.facebook.com/pages/Excel-Formula-Translator/270360316327164). And I have created an app there, so that the translator can directly be called from a tab of the fanpage. PS: hoping for some likes :-)
The translator is now able to translate keywords in the formulas CELL() and INFO(). This means, for example, when entering ZELLE("Dateiname") to translate to English, the output should be CELL("Filename"). The parser expects the keywords at their right argument place. The keyword translation currently only works for German, English and French. This is due to the fact that I have to extract each keyword manually for each language. I will add the keywords step by step.
The translator may be also a little bit faster.
A next step is the recognition of date and time formats; that's not quite simple and a challenge :-)

Best Regards :-)

Bob Phillips
2011-09-11, 01:11 AM
What happened to its online concept?

maninweb
2011-09-11, 09:34 AM
Hi Bob...

This implementation is also work is progress and will take some time.

Best Regards :-)

Bob Phillips
2011-09-11, 12:23 PM
Personally, the online facility is of far more interest to me. The workbook has no interest, so I would think that it would be concentrate on producing a good online version, leave all the fancy fringe bits until later.

maninweb
2011-09-11, 05:01 PM
Hi Bob...

hmm, I am a little bit confused; the current stable online version is here: http://translator.excel-ticker.de and may be always reachable.
I work for the improvements on a copy and when ready update the published version.

Best Regards :-)

maninweb
2011-09-19, 07:25 PM
Hi all...

I have updated the translator on its website to version 1.50 Build 150911, new features are...

Excel error codes like #VALUE! are now also translated when used in ERROR.TYPE(). If you only need the error code, please encapsulate the source error code in a function, for example T().
The arguments for CELL() and INFO() are translated. For example ZELLE("Dateiname") results in CELL("filename") in English or CELLULE("nomfichier") in French.
The translator now includes 32 languages. These languages are all languages I found on MSDN and having a difference to English.
I considered a language to be different to English not only if there was a difference in the Excel functions but also if the error codes and/or arguments for CELL() and INFO() were different. This leads to some cases where a source formula can be equal to a translated formula. Example is Thai, where the only difference I found is INFO("SYSTEM") = INFO("ระบบ").
The layout of the online version has changed and uses listboxes instead of the radio buttons.

Best Regards :-)

Ken Puls
2011-09-26, 08:01 PM
Hi Mourad,

I think your site may be down right now. Can't pull up a page, and your FB fanpage also shows that IE can't connect to the page. Just in case you weren't aware!

maninweb
2011-09-26, 08:17 PM
Hi Ken...

much thanks for the info :)

Ooh, the site for the translator and all (!) my other sites on this server are now down for the second time this day.
This is caused by my provider (1&1) who seems to really have heavy problems with their dedicated servers,
not only in Germany but also in the US and UK, as I had read on Twitter.

So, I hope they get their problems resolved and while waiting for this, sorry for any inconvenience.

Best Regards :-)

Ken Puls
2011-09-26, 08:47 PM
Ouch. I've been there. Frustrating stuff!

maninweb
2011-10-17, 06:37 PM
Hi all...

I have updated the translator to Version 1.75 Build 151011 :)

In fact, for some languages, many formulas have been renamed back in Excel 2010 SP1 to their old names from Excel 2007. So, these names are different in Excel 2010 and Excel 2010 SP1. An example is the Dutch LOOKUP() formula, which is OPZOEKEN() in Excel 2010 and ZOEKEN() in Excel 2010 SP1. Until version 1.50, the translator used the formulas from Excel 2010 without the SP1. This was not intentional, I had overseen, that I had a problem when installing the Language Packs after the Service Pack. The Excel Version showed SP1, but the LP's were not updated.

Now, the translator allows to choose the used Excel Version; default is the last Excel Version.

Best Regards :-)

Tony Jollans
2011-10-17, 09:47 PM
Hi all...

In fact, for some languages, many formulas have been renamed back in Excel 2010 SP1 ...
...
Now, the translator allows to choose the used Excel Version; default is the last Excel Version.



This is a major PITA. Ten languages are affected (see http://support.microsoft.com/kb/2551525) and you must install SP1 for each language separately (several Gb of downloads) to be fully updated but there is no word anywhere that tells you this (not that I have seen anyway). So you think you are on SP1, and Excel tells you you are on SP1, but you still see the pre-SP1 changes in some languages.

maninweb
2011-10-18, 11:19 AM
Hi Tony...

thanks for the tips :-)

I had installed Office and all Language Packs available on MSDN in a virtual machine, unfortunately after I installed the SP1 for Office. And yes, this is really a time consuming thing and the VM takes now 48 GB including Windows 7 and Office. Windows Update has offered two Language Pack SP1 (one about 80 MB and one much smaller), which I also installed. On MSDN, I did not find the SP's for the languages or I had overseen them. However, installing the SP's had no effect, Excel still shows the old formulas. I tried several combinations for uninstalling SP1 for Office and/or Languages and reinstallations, no effect. Please note, that I also posted a thread here on TechNet (http://social.technet.microsoft.com/Forums/de-DE/officesetupdeploy/thread/98bae800-0437-40ef-8994-fd8fd284926e) about these procedures.

Hmm, you said "you must install SP1 for each language separately"; this is something what I did not try; if you are meaning to download the Office 2010 SP1 in each language and the install it. Thanks, I will try this.

Best Regards :-)

maninweb
2011-10-18, 12:44 PM
Hi Tony...

I tried the Office SP1 in Dutch and it works fine, the formulas in Dutch have been updated. Thanks, Tony, for this tip :)
This will save a considerable install time for me; PS: I will also update the thread on TechNet.

Best Regards :-)

Jessica Kloss
2011-11-24, 10:23 AM
nice stuff

aji
2014-01-17, 06:50 AM
ya correct nice stuff ..... :redface:

xynet
2014-09-12, 08:34 AM
Very helpful, thanks

Lilia
2015-10-23, 02:15 PM
Thanks for sharing! This is extremely helpful! :)

maninweb
2015-10-23, 02:42 PM
Hi Lilia,

thanks :)

This thread is a little bit older and I have seen that an old domain is linked here (translator.excel-ticker.de),
The translator moved to its own domain a while ago and had several improvements. The english version
is here http://en.excel-translator.de/translator. The website and tool are also available in several more
languages; (for some languages only partially). You can select the language on the menue on the left.

Best,
Mourad

michelag
2016-01-05, 12:46 PM
that is very cool!

desibouy
2016-02-06, 08:53 PM
Sound Good. Thanks

piaskun79
2016-06-13, 12:59 PM
@maninweb (http://www.excelguru.ca/forums/member.php?199-maninweb)
Great work with translator! Added to my favorites sites.
If you need any help in Polish language, let me know :)

piaskun79
2016-06-13, 02:11 PM
One thing I noticed is decimal separator.
Is it possible to replace dots with commas for Polish language?

For example:

IF(G2<=19.9%,G2+5.5%,"")
is translated to:

JEŻELI(G2<=19.9%;G2+5.5%;"")

Polish Excel do not recognize numbers. Should be 19,9 instead of 19.9

maninweb
2016-06-13, 06:20 PM
Hi piaskun79,

thanks :-)

The formula translator is not yet able to recognize and replace decimal separators in numerical values in Excel formulas. However,
I may implement this in the future - thanks for the suggestion. Implementing this feature will problably lead to some more options
in the interface, because I can not assume, that the defaults of a country is used. Example: I can use Excel in German on an English
Windows OS, so then the decimal separator is a dot and not a comma, if not overridden in Excel.

And thanks for offering help in the polish language. Yes, I am interested in translations of the online tool user interface and other
texts. Would be great, if you can support me. Please contact me at translator (at) maninweb (dot) de.

Best,
Mourad

piaskun79
2016-06-13, 07:34 PM
Good to hear, that you are open for new features :)
Sure, that decimal separator should be as an option, just like separator for the function arguments is now.