Help with Extracting Names from a Cell

[jstarr]

I'm a huge baseball fan and I'm trying to extract a name from a spreadsheet I have. The names are listed with the position, and team all in one cell.

Example below:

[TABLE="width: 300"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Mike Trout CF | LAA [/TD]
[/TR]
[TR]
[TD]Miguel Cabrera 1B | DET


How can I extract Mike Trout, and Miguel Cabrera from cells A3 and A4?[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

Have you tried using the Text To Columns option with the space as the delimiter?

 

[jscranton]

Is the format always the same: First Last Position | Team? If so, use the left function:

=TRIM(LEFT(A1,FIND(" ",A1,FIND(" ",A1,1)+1)))

Where A1 contains your first player's name.

 

[jstarr]

I was hoping I didn't have to go that route, since I'll have to then merge the first name and last name cells back together each time.

 

[Jonmo1]

Is the format always the same: First Last Position | Team? If so, use the left function:

=TRIM(LEFT(A1,FIND(" ",A1,FIND(" ",A1,1)+1)))

that could be problematic if there are any 2 word first names (or last names).

Better to rely on finding the |, assuming at least the Position is consistent (always 2 characters)

=LEFT(A1,FIND("|",A1)-5)

 

[jstarr]

that could be problematic if there are any 2 word first names (or last names).

Better to rely on finding the |, assuming at least the Position is consistent (always 2 characters)

=LEFT(A1,FIND("|",A1)-5)

So the formula works perfectly with 1 exception. Here's a name example: Buster Posey C | SF

In this case, it writes the name as Buster Pose since the position is only 1 letter. Any thoughts on how to resolve for these situations?

 

[wwbwb]

What about:

=LEFT(A1,FIND(" ",A1,FIND("|",A1)-5))

 

[jeffreybrown]

What about: =LEFT(A1,FIND(" ",A1,FIND("|",A1)-5))

Just in case the OP is going to match these names down the road, then maybe this needs to be wrapped in Trim()

=TRIM(LEFT(A1,FIND(" ",A1,FIND("|",A1)-5)))

Just a thought...

 

[jstarr]

That formula is leaving an empty space after it extracts the name. Any idea why?

 

[jeffreybrown]

Can you be more specific? What did you try and on what?

      -----------A----------- ------B------- ----------------------C-----------------------
  1   Mike Trout CF | LAA     Mike Trout     B1:=TRIM(LEFT(A1,FIND(" ",A1,FIND("|",A1)-5)))
  2   Miguel Cabrera 1B | DET Miguel Cabrera                                               
  3   Buster Posey C | SF     Buster Posey