Actually, my first formula only strips text off the back of the cell value.

I believe this formula actually DOES do what you want:

=IF(--(LEFT(A1,MAX((--ISNUMBER(--MID(A1,ROW(1:10),1))*ROW(1:10)))))4000,
VLOOKUP(--(LEFT(A1,MAX((--ISNUMBER(--MID(A1,ROW(1:10),1))*ROW(1:10))))),Sheet1!A2:B263, 2), "S")

Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter]

--
Regards,
Ron