Extract the Third Word from a Text String
The following formula extracts the third word from the text string in A2...
=INDEX(MID(A2,FIND("|",SUBSTITUTE(" "&A2," ","|",ROW(INDIRECT("1:"&LEN(A2)-
LEN(SUBSTITUTE(A2," ",""))+1)))),FIND("|",SUBSTITUTE(A2&" "," ","|",
ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))-
FIND("|",SUBSTITUTE(" "&A2," ","|",ROW(INDIRECT("1:"&LEN(A2)-
LEN(SUBSTITUTE(A2," ",""))+1))))),3)
Based on the sample data, the formula returns "word".
Sample Workbook: Download