+ Reply to Thread
Results 1 to 18 of 18

person name columns help!

Hybrid View

misc.v person name columns help! 05-11-2017, 04:24 AM
shukla.ankur281190 Re: person name columns help! 05-11-2017, 04:26 AM
misc.v Re: person name columns help! 05-11-2017, 04:34 AM
milenia Re: person name columns help! 05-11-2017, 04:28 AM
misc.v Re: person name columns help! 05-11-2017, 04:29 AM
shukla.ankur281190 Re: person name columns help! 05-11-2017, 04:35 AM
misc.v Re: person name columns help! 05-11-2017, 04:43 AM
shukla.ankur281190 Re: person name columns help! 05-11-2017, 06:25 AM
misc.v Re: person name columns help! 05-11-2017, 12:19 PM
misc.v Re: person name columns help! 05-11-2017, 12:37 PM
shukla.ankur281190 Re: person name columns help! 05-12-2017, 02:21 AM
milenia Re: person name columns help! 05-11-2017, 04:48 AM
misc.v Re: person name columns help! 05-11-2017, 05:02 AM
milenia Re: person name columns help! 05-11-2017, 06:04 AM
Jocote46 Re: person name columns help! 05-11-2017, 01:22 PM
misc.v Re: person name columns help! 05-11-2017, 01:52 PM
FlameRetired Re: person name columns help! 05-11-2017, 01:31 PM
misc.v Re: person name columns help! 05-11-2017, 01:43 PM
  1. 05-11-2017, 04:24 AM #1
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    person name columns help!

    I have the following name column:
    MR KEVIN SMITH
    MRS PATTY K SMITH
    MISS DANA KRAUS
    CLAUDIA SMITH
    TERRY W SCALIA
    MS LYDIA H BROWN

    I want to separate the salutation to a SALUTATION column,
    as well as FIRST NAME, MIDDLE NAME and LAST NAME column.

    THE YELLOW BOX CONTAIBOX.jpgNS THE EXAMPLE THAT I WANT.


    Cheers, V

    Please, no macros.
    Last edited by misc.v; 05-11-2017 at 04:52 AM.
    Reply With QuoteRegister To Reply
  2. 05-11-2017, 04:26 AM #2
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,998

    Re: person name columns help!

    Can put some example here how it should look like ??
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 05-11-2017, 04:34 AM #3
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Quote Originally Posted by shukla.ankur281190 View Post
    can put some example here how it should look like ??
    just a quck little snapshot, of column corresponding with textScreenshot_3.jpg
    Last edited by misc.v; 05-11-2017 at 04:38 AM.
    Reply With QuoteRegister To Reply
  4. 05-11-2017, 04:28 AM #4
    milenia is offline
    Registered User
    Join Date
    05-28-2012
    Location
    Hinckley
    MS-Off Ver
    Excel 2003 / 2010 / 2013
    Posts
    7

    Re: person name columns help!

    Easy search for the spaces...

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

    This will extract the Salutation, I would also search to see if this is MR, MRS etc as some of the names you provided didn't have one.
    Reply With QuoteRegister To Reply
  5. 05-11-2017, 04:29 AM #5
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Not all carry salutations.
    Reply With QuoteRegister To Reply
  6. 05-11-2017, 04:35 AM #6
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,998

    Re: person name columns help!

    Assume you have A2 with name

    Try

    B2
    Formula: copy to clipboard 
    =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",99)),(COLUMNS($B2:B2)-1)*99+1,99))


    Drag towards the column SALUTATION, First, Mid & Last name same formula
    Reply With QuoteRegister To Reply
  7. 05-11-2017, 04:43 AM #7
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Quote Originally Posted by shukla.ankur281190 View Post
    Assume you have A2 with name

    Try

    B2
    Formula: copy to clipboard 
    =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",99)),(COLUMNS($B2:B2)-1)*99+1,99))


    Drag towards the column SALUTATION, First, Mid & Last name same formula
    This gives me all first words, but some are not salutations.
    Reply With QuoteRegister To Reply
  8. 05-11-2017, 06:25 AM #8
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,998

    Re: person name columns help!

    Quote Originally Posted by misc.v View Post
    This gives me all first words, but some are not salutations.
    Ok Try

    salutations B2
    Formula: copy to clipboard 
    =IF(AND(LEFT(A2,1)="M",OR(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=3,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2)),LEFT(A2,FIND(" ",A2)-1),"")


    Fiirst Name C2
    Formula: copy to clipboard 
    =LEFT(TRIM(SUBSTITUTE(A2,B2,"")),FIND(" ",TRIM(SUBSTITUTE(A2,B2,"")))-1)


    Mid Name D2
    Formula: copy to clipboard 
    =IF(LEN(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-LEN(SUBSTITUTE(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,"")))," ",""))>0,LEFT(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))),FIND(" ",IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-1),"")


    Last Name E2
    Formula: copy to clipboard 
    =TRIM(IF(AND(B2="",D2=""),SUBSTITUTE(A2,C2,""),IF(B2="",SUBSTITUTE(A2,C2&" "&D2,""),"")))
    Last edited by shukla.ankur281190; 05-11-2017 at 06:27 AM. Reason: Something was missing
    Reply With QuoteRegister To Reply
  9. 05-11-2017, 12:19 PM #9
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Quote Originally Posted by shukla.ankur281190 View Post
    Ok Try

    salutations B2
    Formula: copy to clipboard 
    =IF(AND(LEFT(A2,1)="M",OR(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=3,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2)),LEFT(A2,FIND(" ",A2)-1),"")


    Fiirst Name C2
    Formula: copy to clipboard 
    =LEFT(TRIM(SUBSTITUTE(A2,B2,"")),FIND(" ",TRIM(SUBSTITUTE(A2,B2,"")))-1)


    Mid Name D2
    Formula: copy to clipboard 
    =IF(LEN(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-LEN(SUBSTITUTE(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,"")))," ",""))>0,LEFT(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))),FIND(" ",IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-1),"")


    Last Name E2
    Formula: copy to clipboard 
    =TRIM(IF(AND(B2="",D2=""),SUBSTITUTE(A2,C2,""),IF(B2="",SUBSTITUTE(A2,C2&" "&D2,""),"")))
    This helps a lot, but can you help me understand how your formula works?

    The last name only works on some, and some names start with M, and are 2 letters, or 3 or 4

    IF THERES 5 WORDS THEN MAKE THE LAST 2 AS LAST NAME
    OHDEAR.jpg
    Attached Images Attached Images
    Last edited by misc.v; 05-11-2017 at 01:12 PM.
    Reply With QuoteRegister To Reply
  10. 05-11-2017, 12:37 PM #10
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Quote Originally Posted by shukla.ankur281190 View Post
    Ok Try

    salutations B2
    Formula: copy to clipboard 
    =IF(AND(LEFT(A2,1)="M",OR(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=3,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2)),LEFT(A2,FIND(" ",A2)-1),"")


    Fiirst Name C2
    Formula: copy to clipboard 
    =LEFT(TRIM(SUBSTITUTE(A2,B2,"")),FIND(" ",TRIM(SUBSTITUTE(A2,B2,"")))-1)


    Mid Name D2
    Formula: copy to clipboard 
    =IF(LEN(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-LEN(SUBSTITUTE(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,"")))," ",""))>0,LEFT(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))),FIND(" ",IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-1),"")


    Last Name E2
    Formula: copy to clipboard 
    =TRIM(IF(AND(B2="",D2=""),SUBSTITUTE(A2,C2,""),IF(B2="",SUBSTITUTE(A2,C2&" "&D2,""),"")))
    This one shows, some other ones.
    The only salutations that exist are MR, MRS, MISS, MS
    WALTERS.jpg
    MA, and MIMI are first names
    Reply With QuoteRegister To Reply
  11. 05-12-2017, 02:21 AM #11
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,998

    Re: person name columns help!

    Quote Originally Posted by misc.v View Post
    This one shows, some other ones.
    The only salutations that exist are MR, MRS, MISS, MS
    Attachment 517686
    MA, and MIMI are first names
    OK Try

    B2
    Formula: copy to clipboard 
    =IF(LEFT(A2,1)="M",LEFT(A2,FIND(" ",A2)-1),"")


    C2
    Formula: copy to clipboard 
    =LEFT(TRIM(SUBSTITUTE(A2,B2,"")),FIND(" ",TRIM(SUBSTITUTE(A2,B2,"")))-1)


    D2
    Formula: copy to clipboard 
    =IF(LEN(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-LEN(SUBSTITUTE(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,"")))," ",""))>0,LEFT(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))),FIND(" ",IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-1),"")


    E2
    Formula: copy to clipboard 
    =TRIM(IF(AND(B2="",D2=""),SUBSTITUTE(A2,C2,""),IF(B2="",SUBSTITUTE(A2,C2&" "&D2,""),IF(D2="",SUBSTITUTE(A2,B2&" "&C2,""),SUBSTITUTE(A2,B2&" "&C2&" "&D2,"")))))
    Reply With QuoteRegister To Reply
  12. 05-11-2017, 04:48 AM #12
    milenia is offline
    Registered User
    Join Date
    05-28-2012
    Location
    Hinckley
    MS-Off Ver
    Excel 2003 / 2010 / 2013
    Posts
    7

    Re: person name columns help!

    Assuming you have names starting A2...

    Salutation starting B2
    =LEFT(A2,FIND(" ",A2,1)-1)

    First Name starting C2
    =MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,1)+1)

    Surname starting D2
    =MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)+1,50)
    Reply With QuoteRegister To Reply
  13. 05-11-2017, 05:02 AM #13
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Quote Originally Posted by milenia View Post
    Assuming you have names starting A2...

    Salutation starting B2
    =LEFT(A2,FIND(" ",A2,1)-1)

    First Name starting C2
    =MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,1)+1)

    Surname starting D2
    =MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)+1,50)

    This is what I get.....Screenshot_4.jpg
    Reply With QuoteRegister To Reply
  14. 05-11-2017, 06:04 AM #14
    milenia is offline
    Registered User
    Join Date
    05-28-2012
    Location
    Hinckley
    MS-Off Ver
    Excel 2003 / 2010 / 2013
    Posts
    7

    Re: person name columns help!

    Hi, you will need to do some Data Cleansing first, check for Salutation and how many spaces are present to confirm if there are middle names / initials.
    Reply With QuoteRegister To Reply
  15. 05-11-2017, 01:22 PM #15
    Jocote46 is offline
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: person name columns help!

    you can also use the Text to Column function, that would separate the data the way you want it.
    Reply With QuoteRegister To Reply
  16. 05-11-2017, 01:52 PM #16
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Quote Originally Posted by Jocote46 View Post
    you can also use the Text to Column function, that would separate the data the way you want it.
    What do you mean?
    Reply With QuoteRegister To Reply
  17. 05-11-2017, 01:31 PM #17
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,741

    Re: person name columns help!

    With an exhaustive list of salutations in H1:H4 this array entered formula in B1 filled down and across.
    Formula: copy to clipboard 
    =TRIM(MID(IF(MAX(--(LEN(SUBSTITUTE($A1,$H$1:$H$4,""))<LEN($A1))),REPT(" ",LEN($A1)),
    
REPT(" ",LEN($A1)*2))&SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),COLUMNS($B:B)*LEN($A1)+1,LEN($A1)))

    A
    B
    C
    D
    E
    F
    G
    H
    1
    MR KEVIN SMITH
    MR
    KEVIN
    SMITH
    MR
    2
    MRS PATTY K SMITH
    MRS
    PATTY
    K
    SMITH
    MRS
    3
    MISS DANA KRAUS
    MISS
    DANA
    KRAUS
    MISS
    4
    CLAUDIA SMITH
    CLAUDIA
    SMITH
    MS
    5
    TERRY W SCALIA
    TERRY
    W
    SCALIA
    6
    MS LYDIA H BROWN
    MS
    LYDIA
    H
    BROWN
    Dave
    Reply With QuoteRegister To Reply
  18. 05-11-2017, 01:43 PM #18
    misc.v is offline
    Registered User
    Join Date
    03-27-2017
    Location
    No
    MS-Off Ver
    Yes
    Posts
    30

    Re: person name columns help!

    Quote Originally Posted by FlameRetired View Post
    With an exhaustive list of salutations in H1:H4 this array entered formula in B1 filled down and across.
    Formula: copy to clipboard 
    =TRIM(MID(IF(MAX(--(LEN(SUBSTITUTE($A1,$H$1:$H$4,""))<LEN($A1))),REPT(" ",LEN($A1)),
    
REPT(" ",LEN($A1)*2))&SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),COLUMNS($B:B)*LEN($A1)+1,LEN($A1)))

    A
    B
    C
    D
    E
    F
    G
    H
    1
    MR KEVIN SMITH
    MR
    KEVIN
    SMITH
    MR
    2
    MRS PATTY K SMITH
    MRS
    PATTY
    K
    SMITH
    MRS
    3
    MISS DANA KRAUS
    MISS
    DANA
    KRAUS
    MISS
    4
    CLAUDIA SMITH
    CLAUDIA
    SMITH
    MS
    5
    TERRY W SCALIA
    TERRY
    W
    SCALIA
    6
    MS LYDIA H BROWN
    MS
    LYDIA
    H
    BROWN
    It wasn't just salutations, sorry. It was all of the fields, there is a semi-working version above, but as I started to drag down the formula, i realize, that its a little more complicated because some letters start with M but are not salutations, and some last names carry 2 words, so the entire name may be between 2 words ie "lydia brown" to 5 words ie "mrs lydia fidia de brown" in which case
    salutaion = mrs
    first name = lydia
    middle name = fidia
    last name = de brown
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Run calculations in template for 1 person, copy-paste results, repeat for next person
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2014, 06:02 PM
  2. Sorting a Spreadsheet with multiple columns for one person
    By maverickgt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-16-2014, 08:23 PM
  3. VBA to convert person-to-event into person-person
    By LuckyStrike in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2014, 02:34 PM
  4. A way to send file to certain person, depending on person saving?
    By shiftyspina in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2014, 12:13 PM
  5. [SOLVED] iterate range transpose to columns grouped by person
    By crtallent in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-13-2013, 03:09 PM
  6. Transpose/Pivot multiple rows per person into 1 row per person with fixed columns
    By MaestroEnrique in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2013, 06:35 AM
  7. Search for a person and return all matches including all info on person.
    By JonSparkes in forum Excel General
    Replies: 3
    Last Post: 02-27-2007, 05:27 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1