How to Extract One Word From a Cell in Excel With Formulas

Formulas & functions · Updated November 20, 2026

To extract a particular word from text in Excel for Microsoft 365, put the text in A2 and the word number in B2 (1 for the first word, 2 for the second, and so on), then enter =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,B2),"") in C2. The formula returns that word and leaves C2 blank if the requested word is not available. You can also adapt it to return the first or last word.

This is useful for separating a name, product description, address, or other space-separated text without changing the original cell. The examples below treat a word as a group of characters separated by ordinary spaces. Punctuation stays attached to the word; for example, the third word in “Order, packed and shipped” is “and.”

What You’ll Need

  • Excel for Microsoft 365 on Windows, or Excel 2024, for the TEXTSPLIT-based formulas in this guide. TEXTSPLIT is not available in every older Excel release.
  • A cell containing the source text, such as A2, and a destination cell for the extracted result.
  • For the numbered-word method, a cell containing the position to extract, such as B2. Enter a positive whole number: 1 means first word, 2 means second, and so forth.

The formulas use commas to separate arguments, as in standard Excel installations with comma list separators. If your Excel settings use semicolons, replace argument-separating commas with semicolons; do not change the comma inside a quoted text value unless you are changing the delimiter itself.

Step-by-Step

  1. Check how the source text is arranged. Put one complete text string in each source cell, with words separated by spaces. For example, enter “Northwind Retail Group” in A2 and the number 2 in B2. The goal is to return “Retail” in C2. If your text uses commas, tabs, or another character between words instead of spaces, note that delimiter because the formula must match it.

  2. Enter the numbered-word formula. With the source text in A2 and the word position in B2, select C2 and type =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,B2),""). Press Enter. In the example, TEXTSPLIT separates the text into “Northwind,” “Retail,” and “Group”; INDEX returns item 2, so C2 displays “Retail.” IFERROR displays an empty string if B2 asks for a position that does not exist or the source cannot be split.

  3. Use a fixed word number if you do not need a position cell. With the source text in A2, enter =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,3),"") in the result cell to return the third word. Change the final 3 to 1 for the first word, 2 for the second, or another positive position. Here the source cell is A2 and the formula’s final number is the requested word position; no helper cell is needed.

  4. Copy the formula down for a list. If each row has text in column A and its requested position in column B, enter the numbered-word formula in C2, then drag the fill handle—the small square at the lower-right corner of the selected cell—down the column. Excel adjusts A2 and B2 to A3 and B3 on the next row. Check a few results near the bottom of the list, especially if the rows have different numbers of words.

  5. Return only the first word when that is all you need. With the source text in A2, enter =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,1),""). The last argument to INDEX is 1, so it returns the first item in the split text. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words, helping prevent an empty item from being mistaken for a word.

  6. Return the last word instead. With the source text in A2, enter =IFERROR(TEXTAFTER(TRIM(A2)," ",-1),TRIM(A2)). TEXTAFTER with an instance number of -1 returns the text after the last space. If there is no space, the IFERROR fallback returns the trimmed source itself, so a one-word cell still produces its one word. TEXTAFTER requires Microsoft 365 or Excel 2024.

  7. Fill down and keep the original text intact. Place the result in a separate column rather than typing over the source. If you need to send the extracted words without formulas, copy the results and use Home > Paste > Paste Values. This replaces the formulas in the copied destination with their displayed results; it does not alter the original source column if you paste into a different area.

For a version of Excel without TEXTSPLIT, the first word can be extracted with a traditional formula. With the source text in A2, enter =LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1). FIND locates the first space, and adding a space to the end of the text lets the formula work when A2 contains only one word. This formula returns only the first word; it is not a direct replacement for the numbered-word formula. In older Excel, you can also use Data > Text to Columns to split a copy of a column into separate columns, but that changes the layout of the selected data rather than returning one chosen word in a formula cell. Make a copy before using Text to Columns if the original layout matters.

Common Mistakes

Using the wrong word number: The position starts at 1, not 0. A value of 3 requests the third word, and a value greater than the number of words returns a blank because of IFERROR. Confirm that the number in B2 is a positive whole number and that it matches the intended position.

Forgetting that punctuation is part of the result: These formulas split on spaces, not punctuation. If A2 contains “Smith, Jordan,” the first extracted item is “Smith,” including the comma. If you need to remove punctuation, do that as a separate cleanup step; changing the delimiter to a comma would split at commas and would not automatically remove other punctuation.

Expecting multiple spaces to behave like one word boundary: TRIM handles repeated ordinary spaces in these examples, but it does not remove every character that looks like a space. Text copied from websites or other systems may contain nonbreaking spaces, tabs, or line breaks. Those can prevent the split from behaving as expected.

Putting the formula in the source cell: A formula that refers to the same cell where it is entered creates a circular reference or overwrites the source. Keep the original text in A2 and put the formula in a different cell, such as C2.

Assuming Excel will treat any separator as a space: A hyphenated expression such as “blue-green” is one item under a space-based split. Likewise, a comma-separated list remains one item unless you use a comma as the delimiter. Choose a delimiter that truly marks the boundary of the word you want.

Tips

Keep the word position in its own column when you may want to change it later. For example, a worksheet with text in A2:A100 and a requested position in B2:B100 can use one formula pattern in column C. You can change a position from 2 to 3 without editing the formula itself. This is especially convenient when you are reviewing many rows or extracting different positions in different rows.

If your data sometimes has leading or trailing spaces, keep TRIM in the formula. It also collapses repeated ordinary spaces between words, so a value such as “Northwind Retail Group” is treated as three words rather than being disrupted by empty split positions. TRIM does not remove spaces inside a word, and it does not remove nonbreaking spaces that may arrive in pasted data.

For text copied from a web page that contains nonbreaking spaces, replace those characters with ordinary spaces before splitting. With the source text in A2 and the requested word position in B2, use =IFERROR(INDEX(TEXTSPLIT(TRIM(SUBSTITUTE(A2,CHAR(160)," "))," ",,TRUE),1,B2),""). CHAR(160) represents a nonbreaking space; SUBSTITUTE changes it to a regular space, after which TRIM and TEXTSPLIT can process the text. If the source also contains tabs or line breaks, inspect a sample first because those may need their own cleanup treatment.

Before filling a formula through thousands of rows, try it on a few representative cells: one with a single word, one with several words, and one with unusual spacing. This quickly shows whether the assumed separator matches the actual data. Keep an untouched source column so you can compare the extracted result with the original and correct the formula or input if the text format changes.

Troubleshooting

The result is blank. First check whether the requested position is larger than the number of words in the source. IFERROR intentionally turns a missing position into a blank, so temporarily remove IFERROR to reveal an error if you need to diagnose it. Also verify that the formula points to the correct source cell and that B2 contains a number rather than text such as “second.”

You see #NAME?. This commonly means the Excel version does not recognize TEXTSPLIT or TEXTAFTER. TEXTSPLIT and TEXTAFTER require Microsoft 365 or Excel 2024. Update Excel if your account and organization allow it, use the first-word LEFT/FIND formula for that specific task, or split a copy of the data with Data > Text to Columns. Older Excel versions do not support the same numbered-word formula as written here.

The formula returns an unexpected piece of text. Check the delimiter and the raw source. If the text is separated by a slash, comma, tab, or line break rather than a normal space, the formula’s delimiter will not match it. Check for hidden characters by testing a copied sample, or use the nonbreaking-space cleanup formula above if the source was pasted from a web page.

Excel displays the formula instead of a result. Make sure the destination cell is not formatted as Text. Change its format to General, edit the cell by pressing F2, and press Enter. Also check that the formula begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; on Mac, use Control+` where that key is available, or check the Formulas tab for the Show Formulas control.

You get a formula separator error. Some regional settings use semicolons between function arguments. If Excel rejects a formula that uses commas between arguments, use the separator shown by your Excel installation. The space delimiter must still be written as a quoted space, and the quotation marks must be straight double quotes when entering the formula.

Frequently Asked Questions

Can I extract the second word from a cell?
Yes. Put the source text in A2 and enter 2 in B2, then use the numbered-word formula in C2. To always return the second word without a position cell, use the fixed-position formula and change its final INDEX argument to 2.

Does the formula work if the cell contains only one word?
Yes. The numbered-word formula returns that word when B2 is 1. If B2 requests a later word, IFERROR returns a blank. The last-word formula also falls back to the trimmed source when there is no space.

Can I extract a word by its number when the text has several spaces?
Yes, for repeated ordinary spaces. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces to one, while TEXTSPLIT’s ignore_empty argument is set to TRUE. Nonbreaking spaces and other hidden characters may need cleaning first.

Can I extract a word based on a character such as a comma?
Yes, if that character consistently separates the items. Replace the quoted space delimiter in TEXTSPLIT with the character you use, such as a quoted comma. The formula then counts comma-separated items, which may be phrases rather than individual words.

Will the extracted word update when I edit the source cell?
Yes. A formula result recalculates when its referenced source text or position changes, provided Excel calculation is set to Automatic. If you paste values over the result, it becomes fixed text and will no longer update with the source.

Can I remove the formula after extracting the word?
Yes. Copy the result cells and paste values into a destination range using Home > Paste > Paste Values. Keep the formula version if you want the result to update when source text changes; paste values when you need a static result for sharing or export.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.