In Microsoft 365, split the sentence at spaces and pick the word you want: =INDEX(TEXTSPLIT(TRIM(A1)," "),3) returns the third word. In older versions, a SUBSTITUTE and MID formula does the same.
Excel 365INDEX + TEXTSPLIT
Any versionTRIM + MID + SUBSTITUTE
Last wordTEXTAFTER(A1," ",-1)
Excel 365
=INDEX(TEXTSPLIT(TRIM(A1)," "),3)For The quick brown fox jumps, this returns brown. TRIM removes extra spaces first, so double spaces don't count as empty words. Replace 3 with a cell reference to choose the word.
| To get | Formula |
|---|---|
| The last word | =TEXTAFTER(TRIM(A1)," ",-1) |
| The first word | =TEXTBEFORE(A1," ") |
| Words 2 to 4 | =TEXTJOIN(" ",,CHOOSECOLS(TEXTSPLIT(TRIM(A1)," "),2,3,4)) |
| How many words | =COLUMNS(TEXTSPLIT(TRIM(A1)," ")) |
Any version
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1)))| Part | What it does |
|---|---|
SUBSTITUTE(A1," ",REPT(" ",LEN(A1))) |
Replaces each space with a long run of spaces, so every word sits in its own wide slot |
(3-1)*LEN(A1)+1 |
The start of the third slot |
MID(…,LEN(A1)) |
Cuts out that slot |
TRIM(…) |
Removes the padding, leaving the word |
Change the 3 to the word number you need. If the sentence has fewer words, the result is empty.
Other separators
For words separated by commas or hyphens, change the " ": =INDEX(TEXTSPLIT(A1,","),2) gets the second item of a comma-separated list. Use {" ",","} to split on both.