Please consider supporting this website by buying me a coffee.
Thank you for your consideration!
Updated | November 12, 2024
Extract the Third Word from a Text String
The following formula extracts the third word from the text string in B3...
=INDEX(TEXTSPLIT(B3," "),3)
For earlier versions of Excel...
=INDEX(MID(B3,FIND("|",SUBSTITUTE(" "&B3," ","|",ROW(INDIRECT("1:"&LEN(B3)-
LEN(SUBSTITUTE(B3," ",""))+1)))),FIND("|",SUBSTITUTE(B3&" "," ","|",
ROW(INDIRECT("1:"&LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1))))-
FIND("|",SUBSTITUTE(" "&B3," ","|",ROW(INDIRECT("1:"&LEN(B3)-
LEN(SUBSTITUTE(B3," ",""))+1))))),3)
Based on the sample data, the formula returns "Sentimental".
Sample Workbook: Download