Extract n-th word in cell value
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.
Table of Contents
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.
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".
Get Excel *.xlsx file
Extract category
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
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))), […]
I this article I will show you how to get numerical values from a cell range manually and using an […]
The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]
The LEFT function allows you to extract a string from a cell with a specific number of characters, however, if […]
Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]
The image above demonstrates a rather small formula in cell D3 that extracts values in cell B3 based on two […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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.