I have title, first and last name on column A
and
adress, city, state, zip on column B

I need to split all these into individual columns for purposes of a mail
merge.
My challenge is that I can't use text to column because the row contents are
so different: sometimes Ms., Mr and Mrs, no periods, spaces, PO Box vs
address, etc.

I need your wonderful help addressing all the possible variances.

An example is below:

Column A Column B

Ms. Vivianne Smith 2555 Caminito del Rocio Del Mar, CA 92014
Mr. Robert Jones P.O. Box 4425 Lexington, KY 40454
Mr. and Mrs. James Liles 2854 E Chrysanthemum St Silver Val, AZ 85557

Thanks in advance.

Carolina

Hi,

Well any method you want to use would require some level of consistancy in
the raw data. I would still try text to columns, maybe delimiters of space.
Then if necessary rerun the text to columns command a second time on the data
that couldn't be split the first time and change the delimiter.

You may be able to combine the above with some functions such as FIND, LEFT,
RIGHT and MID to parse out the data fully.

The real secret here is to return the data to the initial source and have
them enter it in a consistant manner.

If this helps, please click the Yes button

cheers,
Shane Devenshire

"Carolina" wrote:

I have title, first and last name on column A
and
adress, city, state, zip on column B

I need to split all these into individual columns for purposes of a mail
merge.
My challenge is that I can't use text to column because the row contents are
so different: sometimes Ms., Mr and Mrs, no periods, spaces, PO Box vs
address, etc.

I need your wonderful help addressing all the possible variances.

An example is below:

Column A Column B

Ms. Vivianne Smith 2555 Caminito del Rocio Del Mar, CA 92014
Mr. Robert Jones P.O. Box 4425 Lexington, KY 40454
Mr. and Mrs. James Liles 2854 E Chrysanthemum St Silver Val, AZ 85557

Thanks in advance.

Carolina

You're out of luck.
If you can't define the rule to ask the question, Excel can't find the
answer.
Text to columns is still likely to be your best bet even if you need some
manual manipulation afterwards. It would at least let you split off the
state & the zip from column B.
--
David Biddulph

"Carolina" wrote in message
...
I have title, first and last name on column A
and
adress, city, state, zip on column B

I need to split all these into individual columns for purposes of a mail
merge.
My challenge is that I can't use text to column because the row contents
are
so different: sometimes Ms., Mr and Mrs, no periods, spaces, PO Box vs
address, etc.

I need your wonderful help addressing all the possible variances.

An example is below:

Column A Column B

Ms. Vivianne Smith 2555 Caminito del Rocio Del Mar, CA
92014
Mr. Robert Jones P.O. Box 4425 Lexington, KY 40454
Mr. and Mrs. James Liles 2854 E Chrysanthemum St Silver Val, AZ 85557

Thanks in advance.

Carolina