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

Weird Text Formula Request??? 3
How do I Delete blank cells in a row 3
Overwriting the content of all footers, despite section breaks (xpost mic.pub.word) 3
Sharing the BCM database 3
Need some plotting help - determining which points are farthest apart 5
searching and collating values in multiple sheets 1
phony French doc defrauding holistic healthcare practitioners via web 0
INDIRECT(ADDRESS) inside a VLOOKUP 13

Top