Return middle column of data using array

[confusion123]

I have 3 columns of data.

MyArray() = Cells(1,1)

would return all three columns.

but I only want the middle column.

Is there a quick way of doing that, instead of creating another array and looping?

Thanks

EDIT SORTED:

Application.WorksheetFunction.Index(Myarray, 0, 2)

 

[S_Wish]

Hi,
Did I correclty understand, that you have in cells(1,1) something like "afdsa(separator)hgfds(separator)fdafds"
if yes, what the separator is?

 

[confusion123]

I have data, say in columns 1 to 3 and I just want to retrieve the values in column 2.

Dim MyArray() As Variant

MyArray = Cells(1,1).CurrentRegion.Value

Dim MiddleArray() As Variant

MiddleArray = Application.WorksheetFunction.Index(MyArray, 0, 2)

 

[S_Wish]

As i don`t know what exactly is your data. I`d guess, that you need:
1. read file
2. take 2 column from it
3. place it in column B.

the second item can be done in such way:
if you have a symbol CR (carriage return), then just read it line by line.
in each line you have some separator between columns (i guess it is space). read from 1 space to 2 space in each line.
if you get more detailes I could help you with VBA macro

Sergej.

OR
use text to col, get 2 col.