Text
Extract the first or last word from a cell (single-word safe)
Pull the leading prefix token or trailing word from trimmed text with TEXTBEFORE/TEXTAFTER and IFERROR fallback for 1-word cells.
Handle 1-word cells without #N/A delimiter errors
TEXTBEFORE(TRIM(A2), " ") extracts the first word and TEXTAFTER(TRIM(A2), " ", -1) extracts the final word—as long as at least one space exists.
On a single-word cell like "Confirmed", no space delimiter is present and bare TEXTBEFORE/TEXTAFTER returns #N/A. Wrapping in IFERROR(..., TRIM(A3)) returns the single word itself cleanly.
Here word means an ordinary-space-separated token. Normalize tabs, line breaks or nonbreaking spaces separately; punctuation is kept with the token.
| Phrase (A) | First Word | Last Word |
|---|---|---|
| URGENT: replace valve #4 | URGENT: | #4 |
| Confirmed | Confirmed | Confirmed |
First-word and last-word formulas
Always TRIM first; otherwise a leading space makes TEXTBEFORE return "" and a trailing space makes TEXTAFTER(..., " ", -1) return "".
=IF(COUNTBLANK(A2:A2)=1,"",LET(t, TRIM(A2), IFERROR(TEXTBEFORE(t, " "), t)))Last-word formula with negative instance -1
Instance -1 searches from the end of the trimmed phrase, returning "#4" on row 2 and "Confirmed" on row 3.
=IF(COUNTBLANK(A2:A2)=1,"",LET(t, TRIM(A2), IFERROR(TEXTAFTER(t, " ", -1), t)))Try the example
Use the original values or change them to test your understanding.