IF FIND LEFT help

[ScabbyDog]

Cell A1 contains for example Joe G Jonathan Yellow OR it will contain Joe G Jonathan Red OR it will contain Joe G Jonathan Substitution. I want to return the value of Joe G Jonathan. Possible? I'm thinking its an IF, Or, Left, Find combined function?

[ScabbyDog]

Also, A2 cell contains "Joe G Jonathan Time In77'" or it could contain "Joe G Jonathan Time In77' Yellow". If it contains either of these I want a formula so I can return just the 77'

[oeldere]

please show us the excel file.

[ScabbyDog]

Here you go. Thanks!

sample.xlsx

[Ace_XL]

This will give you "Joe G Jonathan"
=IF(COUNTIF(A2,"Joe G Jonathan*"),"Joe G Jonathan","")

This will give you the numeric value
=LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$10000))))*1&"'"

[ScabbyDog]

Joe G Jonathan could be any name though.....so that won't work ACE.