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]

 

[jstarr]

Trim fixed it!

Thanks. What does that do?

 

[jeffreybrown]

Removes duplicate spaces, and spaces at the start and end of a text string

Also removes extra spaces between words

Excel Functions List

 

[István Hirsch]

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

TRIM can be omitted in this case:

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

 

[jeffreybrown]

TRIM can be omitted in this case:

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

And I'm sure if we wait long enough there will be another suggestion so I'm not seeing why the TRIM hurts or needs to be omitted

 

[István Hirsch]

Or this one:

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

 

[István Hirsch]

And I'm sure if we wait long enough there will be another suggestion so I'm not seeing why the TRIM hurts or needs to be omitted

I think TRIM function goes through the whole string, checks that the character under test is a space or not, if it is, checks if the next character is also a space, if so, deletes the second space and so on. To subtract 1 seems faster and simpler than that procedure.

 

[jeffreybrown]

Seems it all boils down to user preference and spreadsheet efficiency.