Regional Settings / Finding out Array separator characters

[jabaltie]

Hello !

I'm running Excel on a Portuguese Windows XP system.

I'm trying to create some array constants but haven't been able to do
it.

As for VERTICAL arrays, I use the ";" (semicolons) and it works fine,
like this : ={"A";"B";"C"}

However, when I try to create a HORIZONTAL array, I try to use the
"," (commas) as separators but I can't even enter the formula. It
complains.

How do I find out WHICH is the Array separator character in this
case ?

Notice that in Portuguese, Decimal point is comma.

Thanks !

 

[T. Valko]

Notice that in Portuguese, Decimal point is comma.

Then what do they use for decimal numbers, the comma?

10,25
10.25

Look in Control Panel>Regional and Language Optons>Regional Options
tab>click Customize.

It'll show you a list of settings including the list separator.

Maybe the decimal point is the horizontal array separator: {1.2.3.4.5}

I'm glad I don't have to deal with international issues like this!

 

[Ron Rosenfeld]

Hello !

I'm running Excel on a Portuguese Windows XP system.

I'm trying to create some array constants but haven't been able to do
it.

As for VERTICAL arrays, I use the ";" (semicolons) and it works fine,
like this : ={"A";"B";"C"}

However, when I try to create a HORIZONTAL array, I try to use the
"," (commas) as separators but I can't even enter the formula. It
complains.

How do I find out WHICH is the Array separator character in this
case ?

Notice that in Portuguese, Decimal point is comma.

Thanks !

If you are using the standard settings, then your array separators should be

Columns: \
Rows: ;

If not, the Macro below may be of some help:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.


=================================
Option Explicit
Sub ArraySeps()
MsgBox ("Column Separator:" & vbTab & _
Application.International(xlColumnSeparator) _
& vbLf & "Row Separator:" & vbTab & _
Application.International(xlRowSeparator) _
& vbLf & "Alternate array item separator:" & vbTab & _
Application.International(xlAlternateArraySeparator))
End Sub
====================================

--ron

 

[Ron Rosenfeld]

Look in Control Panel>Regional and Language Optons>Regional Options
tab>click Customize.

Any idea where this is in Windows 7 Professional?
--ron

 

[T. Valko]

No idea.

--
Biff
Microsoft Excel MVP

Any idea where this is in Windows 7 Professional?
--ron

 

[Ron Rosenfeld]

No idea.

The regional and language settings tab is there, but I cannot find the array
separators. (Although they are accessible from the international property of
the Excel application.)
--ron

 

[T. Valko]

In Win Xp there is no specific category for "array" separators. It's listed
as "list" separator. In my U.S. English version the list separator is a
comma which we know is used as the horizontal array separator. There's
nothing in the Regional and Language settings (that I can find) that defines
a vertical "list" (array) separator which we know is the semi-colon.

 

[Ron Rosenfeld]

In Win Xp there is no specific category for "array" separators. It's listed
as "list" separator. In my U.S. English version the list separator is a
comma which we know is used as the horizontal array separator. There's
nothing in the Regional and Language settings (that I can find) that defines
a vertical "list" (array) separator which we know is the semi-colon.

I see. In that case, I wonder if the row separator is different from the list
separator (although they happen to use the same character in this instance).
Maybe I'll look into it further if I ever get a round tuit.
--ron

 

[T. Valko]

If all else fails, as a last resort you could transpose the array.

Since they know a vertical array uses the semi-colon transpose that into a
horizontal array:

TRANSPOSE({1;2;3})

 

[Ron Rosenfeld]

If all else fails, as a last resort you could transpose the array.

Since they know a vertical array uses the semi-colon transpose that into a
horizontal array:

TRANSPOSE({1;2;3})

I would be surprised if the macro I provided did not enable the OP to sort this
out, using the appropriate separators.
--ron

 

[VBasic]

Hello !

I'm running Excel on a Portuguese Windows XP system.

I'm trying to create some array constants but haven't been able to do
it.

As for VERTICAL arrays, I use the ";" (semicolons) and it works fine,
like this : ={"A";"B";"C"}

However, when I try to create a HORIZONTAL array, I try to use the
"," (commas) as separators but I can't even enter the formula. It
complains.

How do I find out WHICH is the Array separator character in this
case ?

Notice that in Portuguese, Decimal point is comma.

Thanks !
.

 

[VBasic]

Hi,
1. INFO
open a new Excel document
open Visual Basic Editor
in the help box type International
select international property
scroll down
there's the info

2. CODE
in Visual Basic Editor select insert new module
copy & paste the subceeding code:
Option Explicit
Sub ArraySeparators()
Dim strsep As String
'Set Application.International(xlColumnSeparator) = "\"
strsep = "Alternate Array Separator =" &
Application.International(xlAlternateArraySeparator) & vbCrLf
strsep = strsep & "Column Separator =" &
Application.International(xlColumnSeparator) & vbCrLf
strsep = strsep & "Decimal Separator =" &
Application.International(xlDecimalSeparator) & vbCrLf
strsep = strsep & "List Separator =" &
Application.International(xlListSeparator) & vbCrLf
strsep = strsep & "Row Separator =" &
Application.International(xlRowSeparator) & vbCrLf
strsep = strsep & "Thousands Separator =" &
Application.International(xlThousandsSeparator) & vbCrLf
MsgBox (strsep)
End Sub

select Run to run the macro

3. CONCLUSION
Now you can see your separators in the message box. They are read-only which
probably means they can't be changed via Excel or Visual Basic Editor.
I have the English version of Excel 2003 Professional, but Croatian Regional
Settings
I have "Alternate Array Separator" set to @, and "Column Separator" set to
\. When I try to use them, Excel doesn't like them (The formula you typed
contains an error...).
Is it the end of the road?
Come on, you experts!