Parseing Text strings

[db_murray]

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

 

[Pete_UK]

Highlight the column with the data in and use Data | Text-to-columns
specifying a space as the delimiter. Your date will be split, but you
can easily recombine this if you want to.

Hope this helps.

Pete

 

[Guest]

Given that the number of characters in the ticker symbol changes, the way I
would do it is Data-->text to columns.

 

[db_murray]

Thanks Pete,

That will work but I need a more dynamic solution.
The option text string is generated by a quote service and insterted in
the cell.
I need it then to be devided up and spread throught my spreadsheet.
I really need it to be done automaticly b/c I don't want the final
users of the spreadsheet to mess with the backend of my work.

Dan

 

[db_murray]

Thanks,

That will work but I need a more dynamic solution.
The option text string is generated by a quote service and insterted in
the cell.
I need it then to be devided up and spread throught my spreadsheet.
I really need it to be done automaticly b/c I don't want the final
users of the spreadsheet to mess with the backend of my work.

Dan

 

[Ron Rosenfeld]

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

 

[db_murray]

Thanks Ron.

 

[Ron Rosenfeld]

Thanks Ron.

You're welcome. Glad to help.

Lot's of other good functions in that add-in.


--ron

 

[Ron Rosenfeld]

Thanks Ron.

By the way, the example I gave just puts it into sequential columns.

To put it into different cells, the third argument ...,columns($A:A)) is the
index number to tell which "word" to parse out. When you drag it across the
row, it reduces to 1 or 2 or 3 or n depending on where you are.

So the first word, that argument would be a 1; word 2 -- 2; etc. You can hard
code it if my method is not appropriate.


--ron