Extract a Name from a Text String

[Eric Alan]

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

[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),"")

[Eric Alan]

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!!!

[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

[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)),"")

[FlameRetired]

Similar. In B3 filled down.

Please Login or Register  to view this content.

in C3 filled down.

Please Login or Register  to view this content.

[Eric Alan]

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

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!!!

[AliGW]

You're welcome!

[Glenn Kennedy]

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.