How to Extract the First and Last Word from a Text in Excel

Sometimes you have a cell containing multiple words and need to extract only the first or the last word.
Excel does not have a built-in function for this, but with a combination of formulas, the solution is simple and reliable.

---

Sample Text

Assume the following city names are in column A:

John Michael Smith
Sarah Ann Johnson
David Robert Miller

---

Extract the First Word from a Text

To extract the first word from a cell, use the formula below:

=LEFT(A1,FIND(" ",A1&" ")-1)

How it works (short and direct)

• FIND(" ",A1&" ") locates the position of the first space
• LEFT extracts everything before that space
• Adding " " avoids errors if the text has only one word

Result:

John
Sarah
David

---

Extract the Last Word from a Text

Extracting the last word is slightly more complex because Excel reads text from left to right.
For this, we combine multiple functions.

Formula

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

---

How it works (simplified)

• LEN(A1) counts the total number of characters
• REPT(" ",LEN(A1)) creates a large number of spaces
• SUBSTITUTE replaces each space with those extra spaces
• RIGHT extracts the last section of the text
• TRIM removes unnecessary spaces

Result:

Smith
Johnson
Miller

---

Common Issues

• Extra spaces in the text → TRIM solves this
• Single-word text → both formulas still work correctly
• Empty cells → formulas return blank results

---

Final Notes

With these formulas, you can reliably extract the first and last word from any text string in Excel.
The first-word formula is straightforward, while the last-word formula looks complex but only needs to be understood once.