+ Reply to Thread
Results 1 to 15 of 15

left, right, find, mid

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    Houston, TX, USA
    MS-Off Ver
    Excel 2016
    Posts
    58

    left, right, find, mid

    I have names in an Excel spreadsheet that are typed with the first and last name together. I need them separated into two columns, first name and last name. I have tried using the left function with the find function (to find the space after the first name) but I am not doing it correctly. Some names (of couples) have both first names together. I would separate those onto two lines.

    Also, once I get the names separated, I need the last name to appear in all caps. Excel does not have a font/all caps feature like Word does. Any suggestions?

    Please use left and right -- and mid, only if you have to.

    Thanks for your help.
    Attached Files Attached Files
    Last edited by phillyfilly; 03-06-2017 at 04:58 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,879

    Re: left, right, find, mid

    This will split out the 1st and last names...
    1st name=LEFT(A2,FIND(" ",A2)-1)
    last name=MID(A2,FIND(" ",A2)+1,99)

    for the couples, show what you want please, and will it always be a & between them, or maybe something else?

    Keep in mind "Some names (of couples) have both first names together. I would separate those onto two lines." no formula cam move data, so you wont be able to put 1 part on 1 row and another part on another row, if it means moving all the next rows of data down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-07-2012
    Location
    Houston, TX, USA
    MS-Off Ver
    Excel 2016
    Posts
    58

    Re: left, right, find, mid

    On a name like Louise Woods Mitchell, I need FN Louise / LN Mitchell, not LN Woods Mitchell.

    For the last name, why did you use the MID function instead of the right function?

    For Henry & Mary King, I need Henry King not FN Henry, LN & Mary King

    I will add a row and retype Mary & Henry King to get Mary's names

    It will always be an & on the couples. Thanks.
    Last edited by phillyfilly; 03-06-2017 at 03:36 AM.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,895

    Re: left, right, find, mid

    pls attach a sample excel file with desired result
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: left, right, find, mid

    Update to original post invalidates reply

    Please use left and right -- and mid, only if you have to.
    Also - no mention was made of Name and initial so that case was not handled.
    Last edited by cytop; 03-06-2017 at 07:38 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,879

    Re: left, right, find, mid

    Why did you remove the sample file?

  7. #7
    Registered User
    Join Date
    12-07-2012
    Location
    Houston, TX, USA
    MS-Off Ver
    Excel 2016
    Posts
    58

    Re: left, right, find, mid

    I wanted to change the names. Here's a new copy. Thanks.
    Attached Files Attached Files
    Last edited by phillyfilly; 03-06-2017 at 05:01 PM.

  8. #8
    Registered User
    Join Date
    12-07-2012
    Location
    Houston, TX, USA
    MS-Off Ver
    Excel 2016
    Posts
    58

    Re: left, right, find, mid

    Here is sample file. Thanks.
    Attached Files Attached Files

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2402
    Posts
    13,391

    Re: left, right, find, mid

    Try this in B2 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in C2 and copied down is the simplest way I could find to resolve the titled Frank E Richardson III.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If there are other anomalies they will require more complex solution ... perhaps a lookup table.



    A
    B
    C
    1
    Name
    FirstName
    LastName
    2
    Louise Woods Mitchell
    Louise
    Mitchell
    3
    Robert J. Clark
    Robert
    Clark
    4
    William & Tommie Smith
    William
    Smith
    5
    Tommie P &William Smith
    Tommie
    Smith
    6
    Jane Doe-Henry
    Jane
    Doe-Henry
    7
    Mary A. Brown
    Mary
    Brown
    8
    Angela Roberts-Caldwell
    Angela
    Roberts-Caldwell
    9
    Charles H. & Katherine Williams
    Charles
    Williams
    10
    Katherine Williams
    Katherine
    Williams
    11
    Pamela Grainger Edwards
    Pamela
    Edwards
    12
    Harry Robinson
    Harry
    Robinson
    13
    Deborah Boatwright-Davidson
    Deborah
    Boatwright-Davidson
    14
    Frank E Richardson III
    Frank
    Richardson III
    Dave

  10. #10
    Registered User
    Join Date
    12-07-2012
    Location
    Houston, TX, USA
    MS-Off Ver
    Excel 2016
    Posts
    58

    Re: left, right, find, mid

    Thank you very much for the formulas.

    What if the gentleman is a Jr or II? I tried substituting for "III" but it doesn't work?

    Thanks.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2402
    Posts
    13,391

    Re: left, right, find, mid

    You will need a lookup table of all anticipated suffixes.

    I should be able to remake the formula in column C work with that.
    Just make a list in an out of the way place and post what you have.

    Also what about the Von Brauns etc.?

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2402
    Posts
    13,391

    Re: left, right, find, mid

    Here is an example of what I mean. In the attached there is a lookup table of suffixes in column J. In addition it is a dynamic named range. If you are not familiar with dynamic named ranges they grow ... and shrink with the data.

    They are referenced by name in the formulas so the lookup range doesn't have to be edited every time there is an addition to the list.

    I named this one Suffix. Its formula in Name Manager is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula in column B remains the same. The new formula in column C is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: left, right, find, mid

    another way
    B2: =regex(A2,"(^\S*)")
    C2: =regex(A2,"(\S+)(?=$)|(\S+\s[IVXCL]{1,3}$)")

    but first you will need this: VBA Regex

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2402
    Posts
    13,391

    Re: left, right, find, mid

    Given the immense variety of names there could be problems with my former lookup table. For example: Srijani Singh

    If you append "." to each of the lookup items and coerce a trailing "." to the Name this formula works ... at least so far.
    Names like Kurt Vonnegut or Wernher Von Braun might be a problem, but appending a space character to those in the lookup should work most of the time.

    If there are Von Braun III, Von Braun Jr ... etc I am out of ideas.



    J
    1
    Von
    2
    VI.
    3
    Van
    4
    V.
    5
    Sr.
    6
    Ret.
    7
    Jr.
    8
    IV.
    9
    III.
    10
    II.
    11
    Esq.
    12
    Dr.
    Attached Files Attached Files
    Last edited by FlameRetired; 03-07-2017 at 11:50 PM.

  15. #15
    Registered User
    Join Date
    12-07-2012
    Location
    Houston, TX, USA
    MS-Off Ver
    Excel 2016
    Posts
    58

    Re: left, right, find, mid

    FDibbins,
    Did you get my new sample file?

+ 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. Left, right, Find all together
    By ankir in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-31-2016, 10:13 AM
  2. LEFT, RIGHT and FIND
    By msixdatateam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2014, 11:59 AM
  3. [SOLVED] Left/right find
    By jwillis07 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2014, 04:29 AM
  4. [SOLVED] Left with find?
    By namluke in forum Excel General
    Replies: 7
    Last Post: 06-14-2014, 02:51 PM
  5. =FIND from right instead of left?
    By mewingkitty in forum Excel General
    Replies: 3
    Last Post: 11-27-2008, 04:16 AM
  6. Left, Right, Mid, Find...
    By Petitboeuf in forum Excel General
    Replies: 4
    Last Post: 02-27-2007, 11:12 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