Get the First and Last Word from a String in Excel

Splitting a cell on its spaces is one of those jobs that looks like it should take five seconds and then eats twenty minutes. The formulas below are short, but each one has a specific reason for the parts that look redundant — and those parts are what stop the formula breaking on the one row you did not check.

Throughout, the text sits in A2.

How to get the first word in Excel

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

Say A2 contains Margaret Hamilton Apollo. FIND(" ",A2&" ") returns 9, the position of the first space. Subtract one and LEFT takes the first 8 characters: Margaret.

The &" " is the part people leave out, and it is the part that matters. If A2 contains a single word with no space at all — Total, say, or a blank-looking cell — then plain FIND(" ",A2) returns #VALUE!, because it cannot find what is not there. Gluing a space onto the end guarantees there is always one to find. For Total, FIND now returns 6, LEFT(A2,5) returns Total, and the formula quietly does the sensible thing instead of erroring.

How to get the last word in Excel

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

This one looks strange until you watch it run. Take A2 = North West Region, which is 17 characters long.

1. REPT(" ",LEN(A2)) builds a block of 17 spaces.
2. SUBSTITUTE replaces every space in the text with that 17-space block, giving North · 17 spaces · West · 17 spaces · Region.
3. RIGHT(...,17) grabs the last 17 characters. Because the gaps are now enormous, those 17 characters can only be padding plus the final word: 11 spaces and Region.
4. TRIM strips the padding and leaves Region.

Using LEN(A2) for the padding rather than a fixed number is deliberate. Plenty of versions of this formula float around with REPT(" ",100) hard-coded, which fails silently the day someone pastes in a cell whose last word is longer than 100 characters. The padding can never need to be wider than the whole string, so LEN(A2) is always safe and never wasteful.

TEXTBEFORE and TEXTAFTER: the short way

Microsoft added two functions that do this directly:

=TEXTBEFORE(A2," ")      first word
=TEXTAFTER(A2," ",-1)    last word

The -1 is the interesting argument. A negative instance number tells TEXTAFTER to count delimiters from the end of the string, so “the text after the last space” is exactly the last word.

Both return #N/A when the cell holds a single word with no delimiter. Rather than wrapping the whole thing in IFERROR, use the built-in sixth argument, if_not_found:

=TEXTBEFORE(A2," ",1,,,A2)

If there is no space, return the cell itself — which for a one-word cell is the right answer anyway.

One caveat before you standardise on these: TEXTBEFORE and TEXTAFTER are available in Microsoft 365, Excel 2024 and Excel for the web. They do not exist in Excel 2021, 2019 or 2016. If your workbook travels to colleagues on older builds, they will see #NAME?, and you want the classic formulas instead. If you need to check behaviour across builds, see running two versions of Excel side by side.

Every variant, side by side

What you want	Microsoft 365 / Excel 2024	Works in any version
First word	=TEXTBEFORE(A2," ")	=LEFT(A2,FIND(" ",A2&" ")-1)
Last word	=TEXTAFTER(A2," ",-1)	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
Second word	=TEXTBEFORE(TEXTAFTER(A2," ")," ")	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)+1,LEN(A2)))
Nth word (N in C2)	=TEXTBEFORE(TEXTAFTER(A2," ",C2-1)," ")	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(C2-1)*LEN(A2)+1,LEN(A2)))
Everything after the first word	=TEXTAFTER(A2," ")	=MID(A2,FIND(" ",A2&" ")+1,LEN(A2))
Everything before the last word	=TEXTBEFORE(A2," ",-1)	=TRIM(LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))))))

The nth-word pattern is worth understanding rather than copying. Once every space has become a block of LEN(A2) spaces, each word sits in its own slot of that width, so MID starting at (N-1)×LEN(A2)+1 lands somewhere inside word N with padding on both sides. TRIM does the rest.

Four things that break these formulas

Trailing and double spaces. A cell that reads North West with two spaces, or North West with one on the end, throws off both the first-word and nth-word formulas. Clean it at the source: =LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1). The last-word formula is immune, because TRIM is already doing the work.

Non-breaking spaces. Text copied from a web page or a PDF often carries CHAR(160) instead of a normal space. It looks identical on screen and FIND(" ",...) walks straight past it. TRIM will not remove it either. Convert first: =SUBSTITUTE(A2,CHAR(160)," "), then apply the formula to that.

Numbers and dates. These are text functions, so they operate on what is stored, not what you see. A date shown as 10 September 2026 is stored as a serial number and the formula returns a fragment of that number. Wrap it in TEXT() first if you genuinely need the displayed form.

Reaching for Flash Fill instead. Ctrl+E is excellent for a one-off split — type the first word manually in B2, press Ctrl+E, and Excel fills the column. But Flash Fill produces static values. Add a row next week and nothing updates. Use it to clean data once; use a formula when the source keeps changing.

When you should not use a formula at all

If you are splitting a column into permanent, separate columns — first name here, surname there — Data → Text to Columns with space as the delimiter is faster and leaves no formulas behind. The same goes for Power Query’s Split Column, which does refresh.

Formulas earn their place when the result has to stay live: a dashboard cell, a lookup key, or the left-hand side of a lookup with multiple criteria. Extracting a surname from a full name to build a match key is one of the most common reasons to do this, and it is a fixture in the Excel tests employers use for jobs.

If you need the row that a value sits on rather than the text inside it, see finding the row number of a matching value.

Frequently asked questions

Sources