# Extract a Name from a Text String

1. ## 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  Register To Reply

2. ## 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),"")  Register To Reply

3. ## Re: Extract a Name from a Text String Originally Posted by Glenn Kennedy 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!!!  Register To Reply

4. ## 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  Register To Reply

5. ## 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)),"")  Register To Reply

6. ## Re: Extract a Name from a Text String

Similar. In B3 filled down.
Formula:  `Please Login or Register  to view this content.`
in C3 filled down.
Formula:  `Please Login or Register  to view this content.`  Register To Reply

7. ## Re: Extract a Name from a Text String Originally Posted by AliGW 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 Originally Posted by Glenn Kennedy 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!!!  Register To Reply

8. ## Re: Extract a Name from a Text String

You're welcome!   Register To Reply

9. ## Re: Extract a Name from a Text String

You're welcome.

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.  Register To Reply

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