Extract k-th word in cell value
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:
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.
Download Excel *.xlsx file
Extract k-th word in cell.xlsx
Related articles
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))), […]
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
Chirag asks: I want to separate numbers from the following text: Abc123bx45 as a result 123 and 45 should be […]
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 […]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
The MID function returns a substring from a string based on the starting position and the number of characters you want […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count specific text string in a cell
Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
Text to columns: Split words in a cell [Array formula]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
The picture above shows you values that have multiple leading and trailing blanks, sometimes also between words. The TRIM function […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish