+ Reply to Thread
Results 1 to 11 of 11

Separate Salutation and Name

  1. #1
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    158

    Separate Salutation and Name

    Hi,

    In the attached demo file I am trying to seperate title and name

    I mean in Column B, I need CAPT, MR,. MRS Dr etc and in column C the name

    How do i do this
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Separate Salutation and Name

    Please supply a list of all possible salutations and a sample sheet that meets our guidelines (see yellow banner at the top of the screen), not 3000 rows.
    Glenn



  3. #3
    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,013

    Re: Separate Salutation and Name

    Can't be done in the B column, but in C3 copied down:

    =LEFT(B3,FIND(" ",B3)-1)

    and in D3 copied down:

    =MID(B3,FIND(" ",B3)+1,99)
    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.

  4. #4
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    158

    Re: Separate Salutation and Name

    Attached Please
    Attached Files Attached Files

  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,013

    Re: Separate Salutation and Name

    Where have you given us a list of all salutations used?

  6. #6
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    158

    Re: Separate Salutation and Name

    It is in Column D please

  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,013

    Re: Separate Salutation and Name

    No. Glenn asked for a single unique list of salutations used. Some of the salutations in column D are not correct (e.g. SQN LDR is showing just as SQN).
    Last edited by AliGW; 02-11-2022 at 04:05 AM. Reason: Typo fixed.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Separate Salutation and Name

    1. The sample is still 3000 rows.

    2. That's not really a list, is it? And it's not even correct.

    Here is a SAMPLE sheet.

    3. Named range "Ranks" - CTRL- F3 to view/edit. Add more ranks, the range will automatically adjust.:

    =Sheet2!$K$1:INDEX(Sheet2!$K:$K,MATCH("Zzzz",Sheet2!$K:$K))


    4. To extract ranks:

    =IFERROR(LOOKUP(1000,SEARCH(" "&Ranks," "&C3),Ranks),"")


    5. To extract the rest:

    =TRIM(SUBSTITUTE(C3,D3,"",1))

    Next time PLEASE make sure you include a SAMPLE sheet, not thousands of rows.
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Separate Salutation and Name

    or, with another Named Range to remove the inconsitency in the results supplied in column C.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    158

    Thumbs up Re: Separate Salutation and Name

    Thank You so much works good

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Separate Salutation and Name

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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. [SOLVED] How can I remove Salutation
    By pop2datop in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 07-16-2017, 09:47 AM
  2. Need help with Salutation
    By SSF1972 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-04-2016, 02:32 PM
  3. [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
  4. Salutation
    By cioccracer1 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 04-22-2013, 01:47 AM
  5. Salutation
    By lardog98 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 06-17-2012, 03:24 PM
  6. Salutation help
    By NED in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2007, 10:16 AM
  7. How can I remove Salutation
    By m_ravindran in forum Excel General
    Replies: 3
    Last Post: 12-09-2005, 10:46 AM

Tags for this Thread

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