+ Reply to Thread
Results 1 to 24 of 24

How can I remove Salutation

  1. #1
    Registered User
    Join Date
    07-16-2017
    Location
    New Delhi
    MS-Off Ver
    2010
    Posts
    5

    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. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    07-16-2017
    Location
    New Delhi
    MS-Off Ver
    2010
    Posts
    5

    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. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    Re: How can I remove Salutation

    Try this:

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

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    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. #6
    Registered User
    Join Date
    07-16-2017
    Location
    New Delhi
    MS-Off Ver
    2010
    Posts
    5

    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    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
    Last edited by AliGW; 07-16-2017 at 08:48 AM.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    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.
    Last edited by AliGW; 07-16-2017 at 08:55 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    Re: How can I remove Salutation

    Try this version:

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

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How can I remove Salutation

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

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    Re: How can I remove Salutation

    Quote Originally Posted by sandy666 View Post
    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. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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
    Last edited by sandy666; 07-16-2017 at 09:07 AM. Reason: forgot comma after word: result

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    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. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,257

    Re: How can I remove Salutation

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

    Mr John Smith John Smith

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How can I remove Salutation

    You too, my sweet Mod

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    Re: How can I remove Salutation

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

  18. #18
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How can I remove Salutation

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

  19. #19
    Registered User
    Join Date
    07-16-2017
    Location
    New Delhi
    MS-Off Ver
    2010
    Posts
    5

    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. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,257

    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. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    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. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    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.
    Last edited by AliGW; 07-16-2017 at 09:44 AM.

  23. #23
    Registered User
    Join Date
    07-16-2017
    Location
    New Delhi
    MS-Off Ver
    2010
    Posts
    5

    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. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,010

    Re: How can I remove Salutation

    You are very welcome! Really glad to have helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help with Salutation
    By SSF1972 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-04-2016, 02:32 PM
  2. [SOLVED] How to Remove salutation from name vba
    By biznez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2016, 01:48 PM
  3. Salutation
    By cioccracer1 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 04-22-2013, 01:47 AM
  4. Salutation
    By lardog98 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 06-17-2012, 03:24 PM
  5. Salutation help
    By NED in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2007, 10:16 AM
  6. How can I remove Salutation
    By m_ravindran in forum Excel General
    Replies: 3
    Last Post: 12-09-2005, 10:46 AM
  7. [SOLVED] How can I create a formula for Salutation
    By cs_vision in forum Excel General
    Replies: 6
    Last Post: 10-07-2005, 07:31 PM

Bookmarks

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