string forumla - before the and, after the and
 

i've got a column with names - mr and mrs - some are seperated by large
space; mr blah mrs blah and some are seperated with an
"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 thats
as far as i get.....

a quick reply would be much appreciated

any queries, let me know
Thanks


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=561032

string forumla - before the and, after the and
 

First use:
=SUBSTITUTE(A1,REPT(" ",10),",")
Then use DataText to ColumnsDelimited with "," separator

HTH
--
AP

"harpscardiff" a
écrit dans le message de news:
...

i've got a column with names - mr and mrs - some are seperated by large
space; mr blah mrs blah and some are seperated with an
"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 thats
as far as i get.....

a quick reply would be much appreciated

any queries, let me know
Thanks


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile:
http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=561032