Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    All Text to Left of a Comma (2003)

    Is it possible extract all text to the left of a comma to pull the last name out of a string of text. For example, "PATTON, JOHN X" is it possible to use a command to pull PATTON out into an adjacent column?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: All Text to Left of a Comma (2003)

    Let's say the values are in column A, starting in cell A2.
    In the cell next to it, B2, enter the following formula:

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

    Fill down as far as needed.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: All Text to Left of a Comma (2003)

    Works great after I put in right reference!!!!

    Thanks.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: All Text to Left of a Comma (2003)

    Using this same process, how would I extract the first name from this?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: All Text to Left of a Comma (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 18-Sep-08 14:05. changed formula to account for double last names)</P>=MID(A1,FIND(", ",A1)+2,FIND(" ",A1&" ",FIND(", ",A1)+2)-FIND(", ",A1)-2)

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: All Text to Left of a Comma (2003)

    Amazing!

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: All Text to Left of a Comma (2003)

    Extract the 1st 2nd and 3rd name

    One formula can do the work

    1] A2 : PATTON, JOHN X

    2] B2 enter the formula, and copied across to D2

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,",","")," ",REPT(" ",50)),COLUMN(A2)*50-49,50))

    Regards
    Bosco

Posting Permissions

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