Extracting the 2nd (Second), 3rd (Third) or 4th (Fourth) Word from a Cell (Excel 2010)

Extracting the 2nd (Second) Word from a Cell
Text is in A6

=MID(MID(MID(SUBSTITUTE(A6,” “,”^”,1),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,1)),256),2,FIND(” “,MID(MID(SUBSTITUTE(A6,” “,”^”,1),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,1)),256))-2)

Extracting the 3rd (Third) word from a Cell

=MID(MID(MID(SUBSTITUTE(A6,” “,”^”,2),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,2)),256),2,FIND(” “,MID(MID(SUBSTITUTE(A6,” “,”^”,2),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,2)),256))-2)

If you want to extract the 4th word, change all the 2’s to 3’s

If you wanted to extract the 50th word, change all the 2’s to 49’s

Note: This does not work for the last word.
Use this for the last word

=RIGHT(A6,LEN(A6)-FIND(“*”,SUBSTITUTE(A6,” “,”*”,LEN(A6)-LEN(SUBSTITUTE(A6,” “,””)))))

Excel Training Course Classes in NYC on live online classes