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?
Last edited by NBVC; 05-06-2011 at 10:11 AM.
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?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I'm looking for a result of "Mrs." and "Dr." respectively in my example above.
Because I don't know any better, I've sorted the column (by color) so that this function will apply only to those cells where the pattern PREFIX1_NAME1_AND_PREFIX2_NAME2 applies.
In other words, I want PREFIX2. In general I'm looking for formulas for all of the above, but right now I'm having trouble getting PREFIX2 in particular.
Thank you!
Maybe this:
=MID(B1, FIND("~", SUBSTITUTE(B1&"~", " ", "~", 3))+1, 100)
"Relax. What is mind? No matter. What is matter? Never mind!"
Middle name of 1st person might bring confuison into result.
"Relax. What is mind? No matter. What is matter? Never mind!"
Or:
=TRIM(MID(SUBSTITUTE(W4," ",REPT(" ",100)),300,100))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@NBVC that worked! Result: Ms.
Now I'm looking up why because I've never used those formulas.
@ZBOR didn't work, sadly. Result: Mrs. Y.
Try using the Evaluate Formula tool from the Formulas tab to see the steps and what is happening.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks