excelformulacell-formatting

Using formulas with german format in Excel?

I use formulas in excel, and I have noticed a weird behavior using a German cell format.

  1. In excel, select A1 an set its format to: (regional) German, Versicherungsnachweis-Nr.
  2. Type "=SUM(B1:B3)" in A1. I used SUM in this example but the result is the same for anything starting with "=".

The behavior I get is that A1 is shown as a string. The format adds "[ ]", so the cell is read as "[=SUM(B1:B3)]", even though the content really starts with "=".

See here a screenshot.

I have found absolutely nothing related to this on the internet, how can I have Excel to recognize the cell as a formula?

Solution

  • The Versicherungsnachweis-Nr. format is translated to this:

    Range("A1").NumberFormat = "\[@\]"

    which forces the formula inside to act as a string. Thus, the formula is not evaluated. If you want it to be evaluated, change the format to general, and concatenate the two [ and ]:

    ="["&SUMME(C1:C3)&"]"

    =SUM() in German is =SUMME(). See this for a reference of all German formulas:

    http://dolf.trieschnigg.nl/excel/index.php?langids=en+de