Extract nth word from text string

In this example, the goal is to extract the nth word from the text string given in column B. The article below explains two approaches. The first approach is based on the new TEXTSPLIT function, which makes it very easy to split text with a custom delimiter. The second approach is a more complicated formula that works in older versions of Excel that do not provide the TEXTSPLIT function. See below for details.

TEXTSPLIT function

The TEXTSPLIT function provides a simple way to solve this problem. As the name implies, TEXTSPLIT will split text into pieces using a custom delimiter. In the example shown, the formula in cell D5 is:

=INDEX(TEXTSPLIT(B5," "),C5)

Working from the inside out, the TEXTSPLIT function is configured to split text at each space (" ") character:

TEXTSPLIT(B5," ") // split at space

Text comes from cell B5. The col_delimiter argument is provided as a single space (" "). The result from TEXTSPLIT is an array that includes each word in the text:

{"Better","the","devil","you","know"}

To retrieve the word at the nth position in this array, we use the INDEX function. The array is returned directly to the INDEX function as the array argument. The row_num argument is provided as a reference to cell C5. With the number 3 in cell C5, INDEX returns the 3rd word in the array:

=INDEX({"Better","the","devil","you","know"},3) // returns "devil"

The result from INDEX is the word "devil". As the formula is copied down, it returns the nth word from each text string in column B, where n is provided in column C. If n is changed to a different number in any row, INDEX will retrieve a new word. Note that if n is greater than the number of words in a given text string, INDEX will return a #REF error.

Removing extra space

The operation of this formula depends on there being a single space between each word. If there is the possibility of more than one space between words, the formula may return incorrect results. To handle this situation, you can use the TRIM function like this:

=INDEX(TEXTSPLIT(TRIM(B5)," "),C5)

The TRIM function will remove any leading or trailing spaces from the text string in column B, and will normalize space between words as well. The resulting text is then passed into the TEXTSPLIT function as before. This ensures that the results from TEXTSPLIT will be consistent.

Legacy Excel

If you are working in an older version of Excel without the TEXTSPLIT function, you can use a more complicated formula to extract the nth word. In the screen below the formula in D5 combines five Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN:

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (C5-1)*LEN(B5)+1, LEN(B5)))

The result in column D is the nth word of the text in column B, where n is given in column C:

At the core, this formula takes a text string and "floods" it with a large number of space characters between words using SUBSTITUTE and REPT. Then it splits the text string between words and cleans up the result with the TRIM function. Working from the inside out, the SUBSTITUTE function is used to replace all single spaces with many spaces:

SUBSTITUTE(B5," ",REPT(" ",LEN(B5))) // replace 1 space with many

The REPT function and LEN function are used to calculate the number of spaces to use:

REPT(" ",LEN(B5)) 

LEN returns 25, so the REPT function returns a string of 25 spaces:

REPT(" ",25) // returns "                         "

Note: the number of spaces needs to be large enough to work in all cases. Using the LEN function to calculate the length of the original text string, and using this as the number of spaces, ensures that the formula will work even for very long words in the original text.

The result from REPT is returned directly to the SUBSTITUTE function:

=SUBSTITUTE(B5," ","                         ") 

SUBSTITUTE then replaces every single space in the text from B5 with 25 spaces. You can think of the result at this point as "islands" of words floating in a sea of space :)

="Better                         the                         devil                         you                         know"

The result from SUBSTITUTE is returned directly to the MID function. The formula uses the MID function to extract the nth word. The start_num is worked out with this snippet:

(N-1)*LEN(B5)+1 // returns 51

This code is designed to calculate the correct starting point to extract text, taking into account the fact that we've already flooded the text with extra space characters. We are just repeating the logic used previously to calculate the number of spaces to use between words. The n-1 adjustment is needed because the extra space occurs between words, i.e. the 3rd word occurs after the 2nd block of space, the 4th word occur after the 3rd block of space, etc. The length of the original text string is 25 characters and n is 3, so we have:

=(3-1)*25+1
=2*25+1
=51

The num_chars argument is provided as the original length of B5, which is 25. With this information, the MID function extracts 25 characters, beginning with character 51. The result is returned directly to the TRIM function:

=TRIM("         devil           ")

TRIM strips the space and returns the final result: "devil".

Alternate formula

It is also possible to extract the nth word from a text string in Legacy Excel with this formula, based on the FILTERXML function.

Text to Columns

Don't forget that Excel has a built-in Text to Columns feature that can split text with a delimiter of your choice. If you just need to get the 3rd word from a lot of text strings, this formula may be more convenient (and dynamic), but Text to Columns is still useful in many situations.