Parse xls cell content into array

[mpothen]

OK, any help would be appreciated...

I'm trying to take the contents of one cell and parse it out into an array.

The cell content looks like:

"John; Joe; Bob; Marty"


Any ideas how??


Thanks!
M

 

[mpothen]

Oh and a point of clarification, all the values are indeed separated by a semicolon, so we can use that to distinguish each value.

 

[Rick Rothstein]

We are talking VB code, right? Try this (change the cell A1 reference as needed)...

YourArray = Split(Range("A1").Value, "; ")

This will load up YourArray (either a dynamic String array or Variant variable) with a zero-based array (no matter what your Option Base may be set to). Also note the space after the semi-colon in the Split function's last arguement... that is because you show your text as having a space there. If that space is not guaranteed to always be there, then use this code instead...

YourArray = Split(Replace(Range("A1").Value, "; ", ";"), ";")

 

[mpothen]

Thanks Rick. Yes VBA for Excel 2007. I'll give it a shot and let you know how it works.

 

[Rick Rothstein]

Thanks Rick. Yes VBA for Excel 2007. I'll give it a shot and let you know how it works.

Please note I edited my original message, so you might want to reread that message to see the additions I added.