+ Reply to Thread
Results 1 to 9 of 9

Extract a Name from a Text String

  1. #1
    Registered User
    Join Date
    04-09-2017
    Location
    Gilbert, AZ
    MS-Off Ver
    365 (Subscription)
    Posts
    20

    Extract a Name from a Text String

    Hello again,

    Attached is the file I'm working with. I'm looking to extract a baseball player's name from a string of text in the input cell using a formula if possible.

    In the file:
    • Column A: The input data - text is always in this format.
    • Column B: I used a series of nested IF(LEFT) statements to extract these numbers.
    • Column C: Desired output (manually entered for now as an example).

    Is there a single formula that would be able to do this? If not, hopefully we can extract the first name to one helper cell and the last name to another helper cell and then it's easy to put them back together again in another cell. Thanks for the help!

    Best regards,
    Eric
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,349

    Re: Extract a Name from a Text String

    Let's simplify column B first:

    =IFERROR(--LEFT(A3,1),"") from A3 down.

    Then in C3, copied down:

    =IF(ISNUMBER(B3),LEFT(MID(A3,4,255),SEARCH(CHAR(160),MID(A3,4,255))-1),"")
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    04-09-2017
    Location
    Gilbert, AZ
    MS-Off Ver
    365 (Subscription)
    Posts
    20

    Re: Extract a Name from a Text String

    Quote Originally Posted by Glenn Kennedy View Post
    Let's simplify column B first:

    =IFERROR(--LEFT(A3,1),"") from A3 down.

    Then in C3, copied down:

    =IF(ISNUMBER(B3),LEFT(MID(A3,4,255),SEARCH(CHAR(160),MID(A3,4,255))-1),"")
    First off, love what you did with Column B. SOOO much easier.

    The formula for Column C is returning "Last, First" - could it be updated to return "First Last" instead?

    Thanks for your VERY quick response!!!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,016

    Re: Extract a Name from a Text String

    Like this maybe?

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    3
    1:*Dozier, Brian*2B – R 1 Brian Dozier
    4
    2:*Grossman, Robbie*DH – S 2 Robbie Grossman
    5
    3:*Mauer, Joe*1B – L 3 Joe Mauer
    6
    4:*Sano, Miguel*3B – R 4 Miguel Sano
    7
    5:*Kepler, Max*RF – L 5 Max Kepler
    8
    6:*Polanco, Jorge*SS – S 6 Jorge Polanco
    9
    7:*Gimenez, Chris*C – R 7 Chris Gimenez
    10
    8:*Rosario, Eddie*LF – L 8 Eddie Rosario
    11
    9:*Buxton, Byron*CF – R 9 Byron Buxton
    Sheet: Lineups

    Excel 2016 (Windows) 32 bit
    B
    C
    3
    =LEFT(A3,FIND(":",A3)-1) =TRIM(LEFT(SUBSTITUTE(MID(A3,FIND(" ",A3)+1,99),CHAR(160),REPT(" ",100)),99))&" "&TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",100)),FIND(":",A3)+2,99))
    Sheet: Lineups
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,349

    Re: Extract a Name from a Text String

    Wrap Ali's formula in my error trap and you're there.

    =IF(ISNUMBER(B3),TRIM(LEFT(SUBSTITUTE(MID(A3,FIND(" ",A3)+1,99),CHAR(160),REPT(" ",100)),99))&" "&TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",100)),FIND(":",A3)+2,99)),"")

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Extract a Name from a Text String

    Similar. In B3 filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in C3 filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  7. #7
    Registered User
    Join Date
    04-09-2017
    Location
    Gilbert, AZ
    MS-Off Ver
    365 (Subscription)
    Posts
    20

    Re: Extract a Name from a Text String

    Quote Originally Posted by AliGW View Post
    Like this maybe?

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    3
    1:*Dozier, Brian*2B – R 1 Brian Dozier
    4
    2:*Grossman, Robbie*DH – S 2 Robbie Grossman
    5
    3:*Mauer, Joe*1B – L 3 Joe Mauer
    6
    4:*Sano, Miguel*3B – R 4 Miguel Sano
    7
    5:*Kepler, Max*RF – L 5 Max Kepler
    8
    6:*Polanco, Jorge*SS – S 6 Jorge Polanco
    9
    7:*Gimenez, Chris*C – R 7 Chris Gimenez
    10
    8:*Rosario, Eddie*LF – L 8 Eddie Rosario
    11
    9:*Buxton, Byron*CF – R 9 Byron Buxton
    Sheet: Lineups

    Excel 2016 (Windows) 32 bit
    B
    C
    3
    =LEFT(A3,FIND(":",A3)-1) =TRIM(LEFT(SUBSTITUTE(MID(A3,FIND(" ",A3)+1,99),CHAR(160),REPT(" ",100)),99))&" "&TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",100)),FIND(":",A3)+2,99))
    Sheet: Lineups
    Quote Originally Posted by Glenn Kennedy View Post
    Wrap Ali's formula in my error trap and you're there.

    =IF(ISNUMBER(B3),TRIM(LEFT(SUBSTITUTE(MID(A3,FIND(" ",A3)+1,99),CHAR(160),REPT(" ",100)),99))&" "&TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",100)),FIND(":",A3)+2,99)),"")
    Those worked perfectly. Thank you both!!!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,016

    Re: Extract a Name from a Text String

    You're welcome!

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,349

    Re: Extract a Name from a Text String

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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] Extract text from a given point in a text string, when data points do not share the given
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2014, 03:57 PM
  2. [SOLVED] Find and extract text string from within another text string
    By huy_le in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2013, 03:22 PM
  3. [SOLVED] Extract text from a string of text (amend formula to include new criteria)
    By robertguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 04:53 PM
  4. Find and extract text string from within another text string
    By huy_le in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2013, 09:01 PM
  5. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  6. [SOLVED] extract text string when you only know the last three letters of that string
    By alison0edwards in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-12-2012, 01:20 PM
  7. Extract small string of text from larger string
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 05:36 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