MID to extract 2nd prefix in form "Mr. X and Mrs. Y"

Hi! First post.

I have data in the form "Mr. X and Mrs. Y" or "Mrs. Y and Dr. X" etc. and I'd like to take out just the "Mrs," or the "Dr." In other words, the text between the 3rd and 4th spaces.

There must be an easier way than using MID and SEARCH with tons of nesting. Using that method, I got this far:

=MID(W4,SEARCH(" ",W4,SEARCH(" ",W4,1)+1)+1,SEARCH(" ",W4,SEARCH(" ",W4,SEARCH(" ",W4,SEARCH(" ",W4,1)+1)+1)+1)-SEARCH(" ",W4,SEARCH(" ",W4,1)+1))

Result: "and Mrs."

Pretty sure that from here I need to tell the formula to start at the 3rd space, but not sure where to put in that additional nested search. Note that I copied entirely from this page: http://office.microsoft.com/en-us/ex...001149850.aspx

Anything else I could do, either using these formulae or another?

So what are the actual results you expect from:

"Mr. X and Mrs. Y" or "Mrs. Y and Dr. X"

and is the pattern always the same?

Maybe this:

=MID(B1, FIND("~", SUBSTITUTE(B1&"~", " ", "~", 3))+1, 100)

Middle name of 1st person might bring confuison into result.

Or:

=TRIM(MID(SUBSTITUTE(W4," ",REPT(" ",100)),300,100))

Try using the Evaluate Formula tool from the Formulas tab to see the steps and what is happening.