How to convert multiple values from single cell to an array

[Guest]

Hi
I have a cell containing mutiple values separated by commas that I need to process as an array
e.g. cell B1 contains the string 4001, 4001A, 4001B, 4002, ...

I am trying to create a function, e.g. myFunction(B1), that takes the string, puts the separate values into an array and processes them one at a time to do a vlookup. It's also got to be quite quick as there will be several hundred of calls to this function in the spreadsheet

Nothing that I've tried in the last five hours will convert the string into an array and I'm tearing my hair out

Any help greatly appreciated.

 

[A.W.J. Ales]

Barry,

Look at the function SPLIT ( in VB )

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Hi,
I have a cell containing mutiple values separated by commas that I need to process as an array,
e.g. cell B1 contains the string 4001, 4001A, 4001B, 4002, ...

I am trying to create a function, e.g. myFunction(B1), that takes the

string, puts the separate values into an array and processes them one at a
time to do a vlookup. It's also got to be quite quick as there will be
several hundred of calls to this function in the spreadsheet.

Nothing that I've tried in the last five hours will convert the string

into an array and I'm tearing my hair out.

 

[Tom Ogilvy]

If this needs to work in versions of xl prior to xl2000 (where split was
introduced), then post back.

 

[Guest]

All I can say is thanks - SPLIT is exactly what I needed - if only I'd asked five hours ago