string forumla - before the and, after the and

  • Thread starter Thread starter harpscardiff
  • Start date Start date
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
 
First use:
=SUBSTITUTE(A1,REPT(" ",10),",")
Then use Data>Text to Columns>Delimited with "," separator

HTH
 
Back
Top