Ignore and/or replace some text in cell

[drluke]

In A1 I have: James Mansfield - Team 100

In B1 I want: Mansfield J I need it this way to be able to do a lookup from another worksheet.

My problem is that I don't know how to get rid of the - Team 100. So far I have

=REPLACE(A1,1(FIND(" ",A1),"")&" "&LEFT(A1,1)

The result I get from this is: Mansfield - Team 100 J

 

[sheetspread]

This?

Excel 2010
	A	B
1	James Mansfield - Team 100	Mansfield J
Sheet17

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))&" "&LEFT(A1,1)

 

[Fluff]

Whilst there will be better ways of doing this.
How about
=MID(A1,FIND(" ",A1)+1,FIND(" - ",A1)-FIND(" ",A1)) & " " & LEFT(A1,1)