Weird Text Formula Request???

[harpscardiff]

In my worksheet, there is a column which has the name of the 1s
applicants and the 2nd applicants all in one string. The majority o
records are separated by an “and”; MR CT Blah and MRS OP blah. Th
other half of the records is separated by a large space;
MR CT Blah MRS OP blah.

I’ve done a count of the space it about 19 25 spaces long.

I did select the column and “text to columns”, but it didn’t wor
entirely, as the mr is too long, I can’t totally divide the columns

Is there any way to separate them using a formula?

Ideally I would like column 1 (Mr) and column 2 (Mrs)

Any queries let me know

It would be much appreciated.

Thanks very much

 

[PY & Associates]

Try

Replace "and" with "," first
Text to column, separate with "," and space, multiple space as one

"harpscardiff" <[email protected]>
wrote in message
news:[email protected]...

 

[harpscardiff]

Sorry - forgot to mention which one I need to modify...

The rows which have AND to seperate is not a problem, the problem i
the rows which is sererated with a large space.

Thanks for quick reply

 

[Alex]

Can you replace " " (that was five spaces) with "," and then do the
text-to-columns?

Alex.