# How can I remove Salutation

1. ## How can I remove Salutation

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

2. ## Re: How can I remove Salutation

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.

3. ## Re: How can I remove Salutation

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

4. ## Re: How can I remove Salutation

Try this:

=SUBSTITUTE(MID(A2,FIND(" ",A2)+1,999999999),"& Mrs ","")

5. ## Re: How can I remove Salutation

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.

6. ## Re: How can I remove Salutation

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.

7. ## Re: How can I remove Salutation

It works here.

Excel 2016 (Windows) 32 bit
A
B
2
Mr John Smith John Smith
3
Mr & Mrs Andrew Williams Andrew Williams
4
Mrs Jenny Hawkbird Jenny Hawkbird
5
Ms Emily Taylor Emily Taylor
6
Prof Steven Thorpewood Steven Thorpewood
7
Mr & Mrs Ray Betts Ray Betts
8
Mrs June Bloggs June Bloggs
9
Mr Peter Piper Peter Piper
10
Miss Helen Betts Helen Betts
11
Ms Sara Bloggs Sara Bloggs
12
Prof John Jones John Jones
13
Mr Paul Simon Paul Simon
14
Dr. Mark Johns Mark Johns
 Sheet: Sheet1

Excel 2016 (Windows) 32 bit
B
2
=SUBSTITUTE(MID(A2,FIND(" ",A2)+1,999999999),"& Mrs ","")
 Sheet: Sheet1

8. ## Re: How can I remove Salutation

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

9. ## Re: How can I remove 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.
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.

10. ## Re: How can I remove Salutation

Try this version:

=SUBSTITUTE(SUBSTITUTE(MID(A2,FIND(" ",A2)+1,999999999),"& Mrs ",""),"& Mrs. ","")

11. ## Re: How can I remove Salutation

or if you want double names do the same with: John Smith John Smith

12. ## Re: How can I remove Salutation

Originally Posted by sandy666
or if you want double names do the same with: John Smith John Smith
What does this mean? Who has said they want double names???

13. ## Re: How can I remove Salutation

@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

14. ## Re: How can I remove Salutation

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.

15. ## Re: How can I remove Salutation

Single "John Smith": bold is "input".

Mr John Smith John Smith

16. ## Re: How can I remove Salutation

You too, my sweet Mod

17. ## Re: How can I remove Salutation

Sandy - I do quote for clarity. See post #9, for example.

18. ## Re: How can I remove Salutation

Hahaha, ok, doesn't matter
we will wait for OP answer

19. ## Re: How can I remove Salutation

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.

20. ## Re: How can I remove Salutation

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.

21. ## Re: How can I remove Salutation

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.

22. ## Re: How can I remove Salutation

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.

23. ## Re: How can I remove Salutation

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.

24. ## Re: How can I remove Salutation

You are very welcome! Really glad to have helped.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1