On 5 Sep 2006 09:36:58 -0700, wrote:

I need to take a text string that has a Stock Option description in a
text string (i.e. ADM Dec 2006 40.0000 put). I want to divide up the
text string and have each word in its own cell. I can not figure out
how to do it. Problems arise b/c the stock symbol can have one to four
characters (i.e. X or MSFT).

What is the best way to do it so it automatically does it? I would
prefer to use some sort of function that counts the spaces and gives
the next word or something like that.

DM

The simplest way to do it with functions is to download and install Longre's
free morefunc.xll add-in from http://xcell05.free.fr/

Then, with your data in A1:An, enter the following:

B1: =REGEX.MID($A1,"\S+",COLUMNS($A:A))

copy/drag across to F1.

Select B1:F1 and copy drag down to Row n.

The problem can also be solved with native functions, but the formulas are more
complex.
--ron