1

Let's say I have a string representing a formula which is valid in a German Excel (only):

=SUMME(B1; C1)

Is there a way I can translate formulas like this one to the current locale of Excel (or any language of choice) assuming the input formula is in a clearly defined language like e.g. German using C# code? The expected output for English would be:

=SUM(B1, C1)

As you can see, it mostly comes down to replacing the function names/keywords with their translated counterparts but this probably requires some formula parsing to be perfect.

Note that under the hood Excel stores formulas language independently already. The same Excel sheet can be opened and edited in any language. The catch is, inputting formulas has to be in the current language or it will be invalid (!). There is no such thing as a universal formula which can be pasted into any language Excel and it will work exactly the same. Not even when they're written in English.

I do not need something like an "Excel formula to C# code converter" but a means of taking the current formula language, target language and formula as input and produces the valid target language formula.

Here is a similar question but using VBA code.

BullyWiiPlaza
  • 17,329
  • 10
  • 113
  • 185
  • I'm not sure how German excel formulas work, but in English `=SUM(B1; C1)` is invalid. You can use `,` for a list or `:` for a range. – HaveSpacesuit Dec 06 '17 at 19:44
  • @HaveSpacesuit not in all English. Some use `;` instead of `,`. – Scott Craner Dec 06 '17 at 19:49
  • @HaveSpacesuit: Yes, in German it would be exactly the following: `=SUMME(B1; C1)`. In English US it's `=SUM(B1, C1)`, my bad: https://support.office.com/en-us/article/SUM-function-043e1c7d-7726-4e80-8f32-07b23e057f89 – BullyWiiPlaza Dec 06 '17 at 19:49
  • Have a look at this: https://superuser.com/questions/955662/switch-calc-or-excel-formulas-to-english-language – Peter Pesch Dec 06 '17 at 19:53
  • I think the easiest workaround is to use one computer in German and one computer in English. And make sure that you are using excel formulas i\on both systems. After a couple of months, you'll find that it becomes easier to translate the functions yourself. – Peter Pesch Dec 06 '17 at 19:55
  • And whenever you've got a formula problem for which you cannot google an answer in German: Simply send the excel file (minus all sensitive data, of course) to the other computer, solve the problem there (using English formulas), and send the excel file back to the german computer. That's how I finally managed to learn the dutch excel formulas ... – Peter Pesch Dec 06 '17 at 19:58
  • 1
    fwiw, VBA is very en-us-centric and any range.formula must be in en-us regional language. In fact, in order to use a different regional system's native functions, you assign to the range.formulalocal property instead. –  Dec 06 '17 at 19:59
  • 1
    If you use VBA to create your formulas then you would use the english version and on the sheet it would appear as the current locale version. Edit - @Jeeped beat me to it... – Tim Williams Dec 06 '17 at 20:00
  • fwiw², VBA also requires a comma as the list or parameter separator character regardless of whether the system's regional setting is a comma or a semi-colon. –  Dec 06 '17 at 20:05

0 Answers0