These formulas work well and are easy to adapt for any word in a string of text – just change the SUBSTITUTE number through out the fomula to 1 less than the word you are looking for. The IFERROR returns a blank but it can be useful to return a character something like “^” instead for lookups.

4th

=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256)))),””)

3rd

=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256)))),””)

2nd

=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256)))),””)

1st

=TRIM(MID(A23,1,FIND(” “,A23&” “)))