Extract the Third Word from a Text String

The following formula extracts the third word from the text string in B2...

=INDEX(MID(B2,FIND("|",SUBSTITUTE(" "&B2," ","|",ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1)))),FIND("|",SUBSTITUTE(B2&" "," ","|",ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))))-FIND("|",SUBSTITUTE(" "&B2," ","|",ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))))),3)

The formula needs to be confirmed with ENTER for the PC or RETURN for the Mac.

Based on the sample data, the formula returns "third".

Download a sample Workbook.