# How to extract the nth word from a text string in Excel

Split the text into words and pick one: =INDEX(TEXTSPLIT(A2," "),2) returns the second word. In older versions use the TRIM, MID and SUBSTITUTE trick.

**Excel:** `=INDEX(TEXTSPLIT(A2," "),$E$1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=INDEX(SPLIT(A2," "),1,$E$1)`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Address | Word |  | Word number | 2 |
| 1420 Lake Street Minneapolis |  |  |  |  |
| 88 Oak Avenue Saint Paul |  |  |  |  |
| 7 Elm Court Edina |  |  |  |  |
| 305 River Road Wayzata |  |  |  |  |
| 12 Pine Lane Duluth |  |  |  |  |

Formula in B2, filled to B6: `=INDEX(TEXTSPLIT(A2," "),$E$1)`

Result: Lake; Oak; Elm; River; Pine

## How it works

TEXTSPLIT breaks the text at each space into a row of words, and INDEX picks the one at position `E1`. Change E1 to 3 and you get "Street", "Avenue" and so on.

The older formula pads every space to 100 spaces, so word n starts somewhere in characters `(n-1)*100+1` to `n*100`; MID takes that block and TRIM removes the padding.

## Other ways

- `=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),($E$1-1)*100+1,100))`: Works in every version and in Google Sheets, for words up to 100 characters long.

## Common mistakes

- Double spaces create empty "words" in TEXTSPLIT. Split the trimmed text, `TEXTSPLIT(TRIM(A2)," ")`, or set its ignore_empty argument to TRUE.
- Asking for a word number larger than the word count returns #REF!. Wrap in `IFERROR(…,"")`.

## Questions

**How do I get the nth item from a comma-separated list?**

Split on the comma instead: `=INDEX(TEXTSPLIT(A2,", "),$E$1)`.

**How do I get the last word?**

Use `=TEXTAFTER(A2," ",-1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/extract-nth-word