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

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

1. Extract n-th word in the cell value

Formula in cell D3:

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

Step 1 - Delete leading and trailing spaces

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

TRIM(B3) returns "Martin Williams".

Step 2 - Replace remaining spaces

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.)

Step 3 - Extract values

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".

Step 4 - Once again delete leading and trailing spaces

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.

Back to top

2. Extract n-th string in cell value - Excel 365

Excel 365 formula in cell D3:

Explaining formula

Step 1 - Split strings in value

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3,," ",TRUE)

becomes

TEXTSPLIT("Martin Williams",," ",TRUE)

and returns

{"Martin"; "Williams"}.

Step 2 - Get k-th string in array

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(TEXTSPLIT(B3,," ",TRUE),C3)

becomes

INDEX({"Martin"; "Williams"},2)

and returns "Williams".

Back to top

Get Excel *.xlsx file

Extract k-th word in cell.xlsx

Extract category

How to create name initials
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Sort and return unique distinct single digits from cell range
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

Extract table headers based on a condition
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

Excel categories

Leave a Comment