CODEWITHSUNDEEP
excelformulacell-formatting
Cedt
Cedt

Reputation: 341

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?

Upvotes: 1

Views: 594

Answers (1)

Vityata
Vityata

Reputation: 43593

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

Upvotes: 1

Related Questions