To pull out just the 2nd word (or 3rd, or last) from a phrase — a middle name, a street name, a status code — the classic SUBSTITUTE + REPT + MID trick works in every version, no TEXTSPLIT required.
The example
Pulling the 2nd word from each phrase.
| A | B | |
|---|---|---|
| 1 | Phrase | 2nd word |
| 2 | the quick brown fox | quick |
| 3 | red blue green | blue |
The formula
The 2nd word (N = 2):
How it works
The trick spaces the words far apart, then takes a fixed slice:
SUBSTITUTE(A2," ",REPT(" ",100))replaces each single space with 100 spaces, spreading the words wide apart.MID(…, (N-1)*100+1, 100)grabs a 100-character window starting where word N now sits.- That window contains word N surrounded by padding spaces;
TRIMstrips the padding, leaving the word. - Change
Nto pick any word; it works regardless of how long the words are.
Excel 365 is simpler: =TEXTBEFORE(TEXTAFTER(A2," ", N-1), " ") gets the Nth word, and =INDEX(TEXTSPLIT(A2," "), N) is even cleaner. The REPT trick is for when you don’t have those.
Try it: interactive demo
Type a phrase and pick a word number.
Variations
Excel 365 way
Index into a split:
First word
Everything before the first space:
Last word
After the final space:
Pitfalls & errors
Words longer than 100 characters would overflow the window. Bump the REPT count up (e.g. 200) for very long words — rare, but possible.
Multiple spaces between words can shift the count. TRIM the source first: replace A2 with TRIM(A2).
Asking for a word that isn’t there returns blank or padding. Guard with IF(N≤word count) if needed.
Practice workbook
Frequently asked questions
How do I extract the nth word from a cell in Excel?
Is there an easier way in Excel 365?
How do I get the last word?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class