How to extract Nth word from a cell in Excel

For example, you have the following the text in a cell “A1” and you’d like to extract the 4th word “extract”:

I want to extract the nth word in this cell

To extract the 4th word “extract” from A1, use the following formula:

=MID(MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256))-2)

The result:

extract

This last one can seem a bit over-whelming. It is the 4 occurrences of the number 3 that determines that we parse out the 4th word. In other words, to get the 5th word all occurrences of the number 3 would need to be changed to number 4. If it was the 2nd word we wanted, we would change all occurrences of the number 3 to number 1.

Please note, the above formula cannot be used to get the first or last word. You can find the formula for extracting the first word here and the formula for extracting the last word here.

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

This entry was posted in Extracting words. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*