less than 1 minute read

If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.

Download the example workbook here: Extract Nth Word from a String.xlsx

```
' syntax
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (Nth_Word_Number-1)*LEN(A1)+1, LEN(A1))) ' sentence in A2, Nth number is in B2
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))
```

With the table set up below, copy the above formula into C2 and drag down to see the results.

~ | A | B | C |
---|---|---|---|

1 |
Short Quote |
Nth Word |
Result |

2 |
Procrastination is the greatest labor saving invention of all time. | 1 | Procrastination |

3 |
Black Holes are where God divided by zero. | 6 | divided |

4 |
A day without sunshine is like, night. | 4 | sunshine |

5 |
Fish and visitors stink after 3 days. | 3 | visitors |

6 |
I’m in shape … round’s a shape, isn’t it? | 5 | round’s |

7 |
He who laughs last didn’t get it. | 3 | laughs |

**Tags:** text

**Categories:** formulas

**Updated:** October 29, 2018