If I have cell A1 name Mr. & Mrs. John Smith I would like to remove Salutation and Leave only John and Jenny Smith. How can I do that? What is the formula?
Please also advice when there is Ms. before the first name what will be the formula.
Thanks
If I have cell A1 name Mr. & Mrs. John Smith I would like to remove Salutation and Leave only John and Jenny Smith. How can I do that? What is the formula?
Please also advice when there is Ms. before the first name what will be the formula.
Thanks
Welcome to the forum!
How is Excel meant to know that John Smith's wife is called Jenny?
Please provide a set of realistic sample data.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali
I really appreciated with your quick response and pointed out the missing data. I am providing you some to the data that can help you to understand what I am after.
Name After Salutation
Mr John Smith John Smith
Mr & Mrs Andrew Williams Andrew Williams
Mrs Jenny Hawkbird Jenny Hawkbird
Ms Emily Taylor Emily Taylor
Prof Steven Thorpewood Steven Thorpewood
Mr & Mrs Ray Betts Ray Betts
Mrs June Bloggs June Bloggs
Mr Peter Piper Peter Piper
Miss Helen Betts Helen Betts
Ms Sara Bloggs Sara Bloggs
Prof John Jones John Jones
Mr Paul Simon Paul Simon
Dr. Mark Johns Mark Johns
I would like a query to remove the Mr, Mrs and in between them “&”, Dr, Prof, Miss, Ms Etc.
Many Thanks
Try this:
=SUBSTITUTE(MID(A2,FIND(" ",A2)+1,999999999),"& Mrs ","")
Can we assume this is now resolved? If so, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
I haven't checked the solution till now, It’s still not giving me result what I am after. Thanks for your efforts.
When I use =SUBSTITUTE(MID(A2,FIND(" ",A2)+1,999999999),"& Mrs ","") it does remove Mrs. However It does not remove rest of the salutation.
For example Mr. & Mrs. Andrew Williams after using you formula what I get is “& Mr. Andrew Williams” and it should be only Andrew Williams.
Am I doing it wrong or incorrect? Please help me.
It works here.
Excel 2016 (Windows) 32 bit
A B 2Mr John Smith John Smith 3Mr & Mrs Andrew Williams Andrew Williams 4Mrs Jenny Hawkbird Jenny Hawkbird 5Ms Emily Taylor Emily Taylor 6Prof Steven Thorpewood Steven Thorpewood 7Mr & Mrs Ray Betts Ray Betts 8Mrs June Bloggs June Bloggs 9Mr Peter Piper Peter Piper 10Miss Helen Betts Helen Betts 11Ms Sara Bloggs Sara Bloggs 12Prof John Jones John Jones 13Mr Paul Simon Paul Simon 14Dr. Mark Johns Mark Johns
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
B 2=SUBSTITUTE(MID(A2,FIND(" ",A2)+1,999999999),"& Mrs ","")
Sheet: Sheet1
Last edited by AliGW; 07-16-2017 at 08:48 AM.
I assume your data is in A1 and down
type in B1 two first names, like
B1: John Smith
B2: Andrew Williams
then select second (B2) and use: Home - Editing - Fill - Flash Fill
It can't possibly. Do you really have Mr. & Mrs. Andrew Williams? Or Mr & Mrs Andrew Williams? Or Mrs & Mr. Andrew Williams? You need to be precise in your answer.For example Mr. & Mrs. Andrew Williams after using you formula what I get is “& Mr. Andrew Williams” and it should be only Andrew Williams.
Last edited by AliGW; 07-16-2017 at 08:55 AM.
Try this version:
=SUBSTITUTE(SUBSTITUTE(MID(A2,FIND(" ",A2)+1,999999999),"& Mrs ",""),"& Mrs. ","")
or if you want double names do the same with: John Smith John Smith
@AliGW
maybe read all my posts
I'm talking to OP
so together:
I assume your data is in A1 and down
type in B1 two first names, like
B1: John Smith
B2: Andrew Williams
then select second (B2) and use: Home - Editing - Fill - Flash Fill
or if you want double names do the same with: John Smith John Smith
pattern is:
Mr John Smith John Smith
Mr & Mrs Andrew Williams Andrew Williams
Mrs Jenny Hawkbird Jenny Hawkbird
Ms Emily Taylor Emily Taylor
Prof Steven Thorpewood Steven Thorpewood
Mr & Mrs Ray Betts Ray Betts
Mrs June Bloggs June Bloggs
Mr Peter Piper Peter Piper
Miss Helen Betts Helen Betts
Ms Sara Bloggs Sara Bloggs
Prof John Jones John Jones
Mr Paul Simon Paul Simon
Dr. Mark Johns Mark Johns
OP didn't define result, it should be: single (John Smith) or double (John Smith John Smith) names
Last edited by sandy666; 07-16-2017 at 09:07 AM. Reason: forgot comma after word: result
Sandy - when your posts do not follow on directly from the one you are responding to, please make it clear by quoting the relevant bit of the post to which you are responding, otherwise the assumption is that you are responding to the one immediately before yours, and that's confusing for everybody. Thanks.
Single "John Smith": bold is "input".
Mr John Smith John Smith
You too, my sweet Mod
Sandy - I do quote for clarity. See post #9, for example.
Hahaha, ok, doesn't matter
we will wait for OP answer
Hi Ali
Could I send you my excel sheet? If you don't mind, I will send you only fields with names
This way you can figure out what is the actual problem and let me know so I can fix that in other worksheets. I am using office 2010, I don't have office 2016.
Many thanks for your efforts.
Attach a sample workbook (not image).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Yes, you can attach it here.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
You will need this copied down:
=SUBSTITUTE(SUBSTITUTE(MID(A3,FIND(" ",A3)+1,999999999),"& Dr. ",""),"& Mr. ","")
Correction! This:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A3,FIND(" ",A3)+1,999999999),"& Dr. ",""),"& Mr. ",""),"& Mr.","")
Your list doesn't match your sample data. In the sample you had Mr & Mrs, where in your real data you have Mrs. & Mr.
Last edited by AliGW; 07-16-2017 at 09:44 AM.
Ali You have been a very kind and lovely person many many thanks for your efforts and help and I also like to thanks to the other for helping and addressing my problem you guys are the best.
I will definitely keeping using this forum for future references and also will tell lots of lots of peoples about the help they can get here.
Thanks a million
PS: Please close this post and if possible please remove excel worksheet which I have uploaded.
You are very welcome! Really glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks