Author: Oscar Cronquist Article last updated on April 12, 2018

The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value.

For example, the fifth word in cell B4 is legs and that string is returned to cell D4.

Formula in cell D3:

=TRIM(MID(SUBSTITUTE(TRIM(B3)," ",REPT(" ",200)), (C3-1)*200+1, 200))

I was inspired by Rick Rothstein's comment from the year 2012 when I made this formula.

If your words are longer than 200 characters change each instance of 200 in the formula above to a higher value.

The delimiting character is a blank (space character). Make sure you change that in the formula if the cell value contains a different string delimiting character.

Explaining the formula in cell C3

The TRIM function deletes all blanks (space characters) except single blanks between strings in a cell value.

TRIM(B3) returns "Martin Williams".

The SUBSTITUTE function replaces each space character in the cell value to 200 space characters. The REPT function repeats the space character 200 times.

SUBSTITUTE(TRIM(B3)," ",REPT(" ",200))

becomes

SUBSTITUTE("Martin Williams"," ","            ")

and returns "Martin           Williams". (I have shortened the string for obvious reasons.)

The MID function returns characters from the middle of a text string based on a start character and a number representing the length.

MID(SUBSTITUTE(TRIM(B3)," ",REPT(" ",200)), (C3-1)*200+1, 200)

becomes

MID("Martin           Williams", (C3-1)*200+1, 200)

becomes

MID("Martin           Williams", 201, 200)

and returns "       Williams".

Lastly, the TRIM function removes all blanks (space characters) except single blanks between strings in a cell value.

TRIM(MID(SUBSTITUTE(TRIM(B3)," ",REPT(" ",200)), (C3-1)*200+1, 200))

becomes

TRIM("       Williams")

and returns "Williams" in cell D3.

Get Excel *.xlsx file

Extract k-th word in cell.xlsx

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Extract last word in cell

The formula demonstrated above in cell range C3:C9 extracts the last word from adjacent cell in column B. =TRIM(RIGHT(SUBSTITUTE(B3, " […]

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]