Problems with Comma in Array Formulas

Hi, everyone!

 

I`m trying to create array formulas, but I`m 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

 

Question Info


Last updated December 18, 2019 Views 2,156 Applies to: Have the experts at Excelchat fix your formulas for free. See how.

Hi, if the comma is the decimal separator try

 

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

If this post is helpful or answers the question, please mark it so, thank you.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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


56 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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)



Best regards
Vijay Verma

Timezone: UTC+05:30, PST+12:30
Availability Hours (UTC) : 05:00 AM - 05:00 PM

17 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.