Extract Words in Excel Using Formulas: Step-by-Step

Formulas & functions · Updated November 20, 2026

To return the first word from text in A2, use =IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2)). For the last word, a particular word, or every word split into separate cells, choose the matching formula below. The examples assume Microsoft Excel for Microsoft 365 on Windows and use a space as the word separator.

What You’ll Need

  • An Excel workbook with the text you want to work with, such as names or phrases in column A.
  • An empty column or row for the formula results. Leave neighboring cells clear if you plan to split a phrase into multiple cells.
  • Excel for Microsoft 365 or Excel 2024 for TEXTBEFORE, TEXTAFTER, and TEXTSPLIT. If you use an older Excel version, use the compatibility formulas in Step 6.

These formulas treat a word as text separated by a space. If your data uses commas, hyphens, tabs, or another separator, you may need to change the delimiter in the formula. The examples use commas between formula arguments, as in standard English-language Excel settings.

Step-by-Step

  1. Set up your source and result columns. Put each phrase in its own cell in column A, starting at A2, and add a heading such as “First word” in B1. For example, A2 might contain Ada Lovelace Byron. Keep the original text in column A so you can check the results and reuse it for another extraction. If your text is in a different column, replace A2 in the formulas with the correct cell reference. Select a blank cell for each result; entering a formula in the source cell would overwrite the text you want to extract.
  2. Extract the first word. With the source phrase in A2, enter this formula in B2 to return the text before its first space. The IFERROR function handles a cell containing only one word by returning that word instead of an error.

    =IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2))

    If A2 contains Ada Lovelace Byron, B2 returns Ada. TRIM removes spaces at the beginning and end, so a leading space does not produce a blank result. It does not remove every repeated space between words, but that does not affect finding the first word.

  3. Extract the last word. With the phrase still in A2, enter this formula in B2, or in another empty result column if you are keeping the first-word formula:

    =IFERROR(TEXTAFTER(TRIM(A2)," ",-1),TRIM(A2))

    The -1 tells TEXTAFTER to find the last space and return what follows it. For Ada Lovelace Byron, the result is Byron. As with the first-word formula, IFERROR makes a one-word entry return itself. TRIM also takes care of spaces at the ends of the phrase.

  4. Return a word by its position. TEXTSPLIT separates the phrase into individual words, and INDEX picks the word you want. With the phrase in A2, enter this in B2 to return the second word:

    =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,2),"")

    For Ada Lovelace Byron, the formula returns Lovelace. The final 2 in INDEX means “second column,” while the 1 means “first row.” TRUE tells TEXTSPLIT to ignore empty items, which helps when there are repeated spaces between words. To return the third word, change the final 2 to 3. If the requested position does not exist, IFERROR returns a blank cell.

  5. Split every word into its own cell. If you want all the words rather than one selected word, enter this formula in B2, with the original phrase in A2:

    =TEXTSPLIT(TRIM(A2)," ",,TRUE)

    The result spills horizontally: for Ada Lovelace Byron, B2 shows Ada, C2 shows Lovelace, and D2 shows Byron. Leave those cells empty so Excel can display the full result. This is a dynamic-array formula, so you do not need to copy it across the row. If you want words listed vertically instead, use this formula in B2 with the same source layout:
    =TEXTSPLIT(TRIM(A2),," ",TRUE)
    The third argument specifies a row delimiter, making the words spill down the column.

  6. Use a formula in older Excel. If TEXTBEFORE and TEXTAFTER are not available, you can extract the first word with LEFT and FIND. With the phrase in A2, enter this in B2:

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

    For Ada Lovelace Byron, the result is Ada. Appending a space inside FIND makes the formula work even when A2 contains just one word. To extract the last word in older Excel, use this formula with the same layout:
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))))
    For Ada Lovelace Byron, it returns Byron. These formulas are less convenient for picking a word in the middle or splitting every word, so use TEXTSPLIT when your Excel version supports it.

  7. Apply the formula to more rows. If the same kind of text continues down column A, select the formula cell and drag its fill handle down, or copy and paste it into the remaining result cells. Excel adjusts A2 to A3, A4, and so on automatically. Check several results, including a short phrase and a one-word entry, before relying on the full column. For a TEXTSPLIT formula, check that the cells where results need to spill are empty in every row.

Common Mistakes

  • Choosing the wrong delimiter. A formula that searches for a space will not split text separated only by a comma or a hyphen. Change the quoted delimiter to the character used in your data. For example, use a comma as the delimiter when the source looks like North,South,East. If delimiters vary from row to row, standardize the data first or build a formula to handle those specific separators.
  • Expecting TRIM to fix every spacing problem. TRIM removes extra regular spaces at the start and end and reduces repeated regular spaces between words in supported Excel behavior, but some imported text contains nonbreaking spaces that look like normal spaces. Those characters may prevent the formula from finding a delimiter. Replace them with regular spaces or clean the source before extracting.
  • Overwriting the source or blocking a spill. Put the formula in a different cell from the original phrase. For TEXTSPLIT, make sure all cells in the output area are empty; an existing value can cause a #SPILL! error. Also avoid placing the result inside a merged cell area.
  • Using an unsupported function or changing the word position incorrectly. TEXTBEFORE, TEXTAFTER, and TEXTSPLIT require Microsoft 365 or Excel 2024. In the INDEX example, the last argument selects the word’s position, so leaving it at 2 always requests the second word.

Tips

  • Keep your source text unchanged and give each output column a descriptive heading, such as First word, Last word, or Second word. This makes the workbook easier to review and helps prevent confusion when you use more than one extraction formula.
  • Use TEXTSPLIT when you need several words from the same phrase. It is usually simpler than creating a separate extraction formula for each position. If you need the results to stay in fixed cells, check the split output before sorting or moving rows; dynamic spill results depend on the formula cell and available space around it.
  • For punctuation attached to a word, such as Byron,, the formula returns the punctuation as part of the text. If you need to remove punctuation, handle that as a separate cleanup step so the extraction formula does not silently change legitimate characters.
  • On current Microsoft 365 versions for Mac and Excel for the web, these formulas generally work the same way. Function availability depends on the Excel version, not simply whether you use Windows or Mac. In installations configured for a different region, Excel may require semicolons instead of commas as argument separators.

Troubleshooting

You see #NAME? Excel may not recognize TEXTBEFORE, TEXTAFTER, or TEXTSPLIT because your version does not include that function, or because its name was entered incorrectly. Check that you have Microsoft 365 or Excel 2024, and confirm the spelling. For first- or last-word extraction in an older version, use the formulas in Step 6.

You see #SPILL! after splitting a phrase. Select the formula cell and look for values, merged cells, or other obstructions in the cells where the words should appear. Clear the obstructing cells or move the formula to a blank area. A result that spills across columns needs room to the right; one that spills down needs room below.

The result is blank or the wrong word. Check the source cell reference and count the words in the phrase. The nth-word formula returns blank when the requested position is not present. If the visible spaces are actually tabs, line breaks, or nonbreaking spaces, a regular space delimiter will not match them. Clean or standardize those characters first, then test the formula on one row.

The formula appears as text instead of calculating. Make sure the cell does not have a leading apostrophe and that its number format is not Text. Change the format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. If Excel displays the formula in every cell, check whether Show Formulas is enabled on the Formulas tab and turn it off.

Excel rejects the formula when you enter it. Check that each opening parenthesis has a matching closing parenthesis and that the quotation marks around delimiters are straight double quotes. If your regional settings use semicolons as argument separators, replace the commas that separate arguments with semicolons. Do not replace the comma inside quoted text unless it is the delimiter you intend to search for.

Frequently Asked Questions

Can I extract the third word from a cell?

Yes. With the phrase in A2, use =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,3),""). The final 3 asks for the third word. For Ada Lovelace Byron, the result is Byron. TEXTSPLIT is available in Microsoft 365 and Excel 2024.

How can I extract the first word when the cell contains only one word?

Use the IFERROR version: =IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2)). With a single word in A2, TEXTBEFORE cannot find a space, so IFERROR returns the trimmed source text instead. This also removes leading and trailing regular spaces.

Can I split words down a column instead of across a row?

Yes. With the phrase in A2, use =TEXTSPLIT(TRIM(A2),," ",TRUE) in a blank cell. The words spill vertically. Ensure the cells underneath are clear so the full result can appear.

Do these formulas remove punctuation?

No. They return the text between delimiters, including punctuation attached to a word. For example, splitting Hello, world! on a space returns Hello, and world!. Clean punctuation separately if your task requires it.

Will these formulas work in Google Sheets?

Some familiar functions are available in both products, but function support and formula details can differ. These steps are written for Excel, so check Google Sheets’ function documentation or adapt and test the formula in Sheets before using it on important data.

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.