string forumla - before the and, after the and

H

harpscardiff

i've got a column with names - mr and mrs - some are seperated by larg
space; mr blah mrs blah and some are seperated with a
"and" mr blah "and" mrs blah.....

how do i construct the formula so mr blah and mrs blah is seperate.
obviously the forumla will be entered in different columns, but that
as far as i get.....

a quick reply would be much appreciated

any queries, let me know
Thank
 
A

Ardus Petus

First use:
=SUBSTITUTE(A1,REPT(" ",10),",")
Then use Data>Text to Columns>Delimited with "," separator

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Seperate first and last name within a cell 3
Extracting and replacing the first word in a String of text 5
Please Help - Trouble with counting occurrences in external file 2
Duplicates in excel that aren't 100% DUPLICATES ... 4
splitting text to multiple columns 5
help needed for sorting 5
Binding a DropDownList in DetailsView 1
Messy Text to Columns 3

Top