Problems with Comma in Array Formulas

Question

Hi, everyone!

Im trying to create array formulas, but Im having trouble with the commas.

Since my system uses Commas as a Decimal separator, Excel auto-corrects it, thus blocking the full use of it.

For example:

When trying to create a simple {=(1,2,3;4,5,6)}, Excel corrects it to {=(1,23;4.56)} - remeber that the comma is decimal separator in my system.

I tried changing the Decimal separator in the Advanced Options, but I had no results.

Can you help me telling me where I am wrong?

P.S.: I use Microsoft Excel 2007, Language Settings is U.S. English in a Windows 7 with Brazilian Portuguese as language settings.

Thank you very much for your help,

Gilberto

Answer 1

Hi Gilberto,

Did you find the answer?

I'm starting with array formulas and I get the same problem. Following the try and error method I find the replace for "," in portuguese setting: for horizontal array try "":

={1\2\3;4\5\6} SHIFT+CTRL+ENTER

Answer 2

Yes. That is correct.

If comma is decimal separator symbol, then \ is used in place of comma in Array.

If semi colon is decimal separator symbol, then \ is used in place of semi colon in Array. (Semi colon is row separator)

Answer 3

Hi, ediardp!

Thanks for the reply!

I tried, but this create a vertical array, instead of a horizontal one.

With {=(1,2,3;4,5,6)}, I'm trying to create something like this:

     A     B    C

1   1     2     3

2   4     5     6

While {=(1;2;3;4;5;6)} would create something like:

    A

1  1

2  2

3  3

4  4

5  5

6  6

Answer 4

Hi, if the comma is the decimal separator try

{=(1;2;3;4;5;6)}