Get the Nth Word
To take this a step further, you can use a formula to extract the exact word that you want from a text string. We will extract the fourth word. Again using the previous example, click in cell B3 and enter in the following formula:
=MID(MID(MID(SUBSTITUTE(A2," ","^",3),1,256),FIND("^",SUBSTITUTE(A2,"
","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A2,"
","^",3),1,256),FIND("^",SUBSTITUTE(A2," ","^",3)),256))-2)
This last one can seem a bit overwhelming. The four occurrences of the number 3 determine that we parse out the fourth word. In other words, to get the fifth word, all occurrences of the number 3 would need to be changed to the number 4. If we wanted the second word, we would change all occurrences of the number 3 to the number 1. This formula should display the word is in cell B3.
