Text
Extract the Nth word from a text string (or trim text to N words)
Normalize irregular spacing with TRIM, pull the exact Nth word with INDEX and TEXTSPLIT, or truncate descriptions to the first N words.
Normalize multiple spaces first with TRIM
Splitting raw imported strings directly on " " creates empty tokens whenever two spaces appear back-to-back. Wrapping the cell in TRIM(A2) collapses consecutive internal spaces into single spaces and strips leading/trailing whitespace.
INDEX(TEXTSPLIT(TRIM(A2), " "), 1, N) then plucks the exact Nth word, while IFERROR(..., "") returns an empty string instead of #REF! when a row has fewer than N words.
| Raw Description (A) | N (B) | Nth Word | First N Words |
|---|---|---|---|
| " Express air freight priority " | 3 | "freight" | "Express air freight" |
| " Express air freight priority " | 9 | "" | "Express air freight priority" |
Extract Nth word and truncate to N words
Use TEXTBEFORE(TRIM(A2), " ", N) wrapped in IFERROR(..., TRIM(A2)) to cap product titles or meta snippets at N words.
=IF(OR(B2<1, B2<>INT(B2)), "Invalid position", IFERROR(INDEX(TEXTSPLIT(TRIM(A2), " "), 1, B2), ""))Truncate text to the first N words
When the phrase has fewer than N spaces, IFERROR falls back to the full trimmed phrase.
=IF(OR(B2<1, B2<>INT(B2)), "Invalid position", IFERROR(TEXTBEFORE(TRIM(A2), " ", B2), TRIM(A2)))Inputs and interpretation
N must be a positive integer; zero has special array semantics in Excel INDEX. These words are space-delimited tokens, not linguistic segmentation. Excel TRIM normalizes ordinary ASCII spaces; normalize tabs, line breaks and nonbreaking spaces separately. IFERROR also masks unrelated input errors, so inspect source data if results are unexpectedly blank.
Try the example
Use the original values or change them to test your understanding.