Split creates the array in memory... you do not have to assign it to an
array variable to access elements from it. In memory, Split(Text,Delimiter)
creates a temporary array (normally awaiting assignment to an array
variable) AND it is a true array so it has elements. Just like if MyArray is
an array, MyArray(1) references the 2nd element (in a zero-based) array,
Split(Text,Delimiter)(1) references the 2nd element of the array created by
the Split function in memory (by the way, all arrays returned from Split are
always zero-based, no matter what the Option Base setting is). All my
statement is doing is grabbing the 2nd element produced by Split'ting your
ExternalAddress using "]" as the delimiter (since there will always be only
one closing square bracket, the sheet name will always appear in the 2nd
array element produced by the Split function), which is a String value, and
feeding it into another Split function call, using the apostrophe as the
delimiter, and that the sheet name part will always be in the 1st element
produced by that Split function call.

Rick


"ExcelMonkey" wrote in message
...
What is the role of the (1) and the (0)? I understand that Split will
spit
the string using the delimiter and pass to an array variable. Do the
numbers
in brackets denote the element of the 1D array that you wish to return the
value of?

Thanks

EM



"Rick Rothstein (MVP - VB)" wrote:

You can do what you want without using Regular Expressions...

ExternalAddress = "'[ABC.xls]Sheet1'!$C$2"
SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0)

Rick


"ExcelMonkey" wrote in message
...
I need a quick way to extract the sheet name from this external address"

'[ABC.xls]Sheet1'!$C$2

A quick way would be to use a regex pattern which takes out everything
between the "]" and "!". Does anyone know what this pattern would look
like?

Any ideas?

Thanks

EM