How to extract the first, last or nth word in Excel

The first word: TEXTBEFORE (Excel 365 / 2024)

The sheet is a shop export where each description starts with the material and ends with the size. TEXTBEFORE returns everything before the first space, so =TEXTBEFORE(A2," ") gives Cotton.

Two cells will trip it up. A3 is just Socks, with no space at all, and TEXTBEFORE returns #N/A when it cannot find the delimiter. A5 starts with a space, so the text before that first space is nothing, and the result is an empty cell. The formula below handles both: TRIM removes the leading space, and the sixth argument, if_not_found, returns the whole cell when there is no space.

The last word: TEXTAFTER with -1

TEXTAFTER's third argument is the instance number, and -1 means "count from the end". So =TEXTAFTER(A2," ",-1) returns the text after the last space: XL.

The fifth argument, match_end, set to 1 treats the edges of the text as a delimiter. With it, a single-word cell returns the word itself instead of #N/A. A trailing space would make the last word empty, so TRIM matters here too.

Any version: LEFT and FIND for the first word

FIND(" ",A2) is the position of the first space, and LEFT takes everything before it. The trick is &" ": adding a space to the end of the text guarantees FIND always finds one, so a single word like Socks returns Socks instead of #VALUE!.

This works in Excel 2010 through 365. For a leading space, use TRIM(A5) in both places.

Any version: the last word with TRIM, SUBSTITUTE and REPT

There is no "find the last space" function in older Excel, so this trick pads the words apart instead. SUBSTITUTE replaces every space with 100 spaces. The last word is now alone in the last 100 characters, so RIGHT(…,100) grabs it with a lot of spaces in front, and TRIM throws those away.

100 works as long as no single word is longer than 100 characters. It is safe for single-word cells and extra spaces without any changes.

The nth word

Use the same padding with MID. Word n starts somewhere in the block of characters from (n-1)*100+1, so =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),(3-1)*100+1,100)) returns the third word, Navy. Put the 3 in a cell to make it adjustable.

In Excel 365 or 2024, =INDEX(TEXTSPLIT(A2," "),3) is shorter. TEXTSPLIT splits at every space and INDEX picks one item. Ask for a word that does not exist, such as the fifth word of Silk Scarf Ivory, and INDEX returns #REF!; wrap it in IFERROR(…,"") if some rows are shorter.

Watch double spaces. A4 is "Wool Beanie Grey OS", so splitting at each space creates an empty item between Wool and Beanie and the third word becomes Beanie. TRIM the cell first, or set TEXTSPLIT's fourth argument (ignore_empty) to TRUE.

Which formula to use

On Excel 365 or 2024, TEXTBEFORE and TEXTAFTER read clearly and handle any delimiter; add the if_not_found or match_end argument for single-word cells. For files that must open in Excel 2019 or older, use LEFT with FIND for the first word and the SUBSTITUTE-REPT trick for the last and nth words.

If the words are people's names, the separate first and last name guide covers middle names and "Last, First" order. If you need every word in its own column, Data > Text to Columns with Space as the delimiter does it in one go.