To return a particular word from a cell, put the text in A2 and the word’s position in B2 (1 for the first word, 2 for the second), then enter =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,B2),"") in C2. This works in Microsoft 365 and Excel 2024; the steps below also cover older Excel versions and ways to handle spaces, punctuation, and common errors.
What You’ll Need
- An Excel workbook containing the text you want to split. The examples use A2 for the text, B2 for a word number, and C2 for the extracted result.
- Microsoft 365 or Excel 2024 for the TEXTSPLIT method. If you have an older version, use the legacy formula or Text to Columns method below.
- A clear idea of what counts as a word. The formulas split at ordinary spaces, so punctuation stays attached to a word, and a hyphenated expression is treated as one word.
Step-by-Step
- Check the cell’s contents. Click the cell with the full text and look at the formula bar to confirm its exact contents. For the examples, enter a sample phrase such as “The green apple is ripe” in A2. In this phrase, “The” is word 1, “green” is word 2, “apple” is word 3, and so on. If your text is in another cell, substitute its address for A2 in the formulas.
- Choose which word to return. Type a number in B2 to identify the word’s position from left to right. Enter 1 for the first word, 2 for the second, or 3 for the third. For the sample phrase, enter 3 if you want “apple.” Keeping the position in its own cell makes it easy to change which word is extracted without editing the formula.
- Enter the formula in the result cell. With the phrase in A2 and the requested word number in B2, enter this formula in C2:
=IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,B2),""). Press Enter. For “The green apple is ripe” and B2 set to 3, C2 returns “apple.” TEXTSPLIT separates the phrase at spaces, TRIM removes leading and trailing ordinary spaces, INDEX returns the word at the selected position, and IFERROR leaves C2 blank if the number is outside the available range. - Fill the formula down for other rows. If each row has a phrase in column A and a word number in column B, select C2 and copy the formula down. You can drag the fill handle, the small square at the bottom-right corner of the selected cell, or copy C2 and paste into the cells below. Excel adjusts A2 and B2 to A3 and B3, then A4 and B4, as it fills the formula. Check a few results against the source text before relying on the whole column.
- Extract the first word when that is all you need. If A2 contains the phrase and you always want its first word, enter this formula in B2:
=INDEX(TEXTSPLIT(TRIM(A2)," ",,TRUE),1,1). For “The green apple is ripe,” the result is “The.” This version does not need a separate word-number cell. TEXTSPLIT is available in Microsoft 365 and Excel 2024. In Excel for the web, the formula works when the function is available in your account; the same cell references can be used in Excel for Mac. - Use a compatible formula in older Excel. For Excel versions without TEXTSPLIT, this formula returns the third word from A2:
=TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),201,100)). With “The green apple is ripe” in A2, it returns “apple.” This older approach pads each word with spaces, then takes the text in the third word’s position. It assumes the target word is no longer than 100 characters; change both 100 values and the 201 offset together if you need a larger limit or a different fixed word position. For example, the start position for word 2 would be 101. - Confirm the result and adjust the output. Compare the extracted value with the matching word in the original cell. If you need to edit the result by hand or send it to another workbook without formulas, copy the result cells and use Home > Paste > Paste Values. Otherwise, keep the formulas so the result updates when the source text or word number changes.
The formulas above split on spaces, not on grammar or meaning. For example, if A2 contains “Wait, stop now!” and B2 is 1, the result is “Wait,” including its comma. If B2 is 3, the result is “now!” including the exclamation mark. Remove punctuation only if your task requires a cleaned word; extraction alone does not decide whether punctuation belongs in the result.
If you need to split text visually rather than create a formula result, select the source cells and choose Data > Text to Columns. Select Delimited, choose Space as the delimiter, and complete the wizard. This places the words in separate columns rather than extracting only a selected word into one result cell. Choose an empty destination area first so the split output does not overwrite existing data.
Common Mistakes
- Counting from zero or counting punctuation as a separate word. Word positions begin at 1. Spaces mark the boundaries; a comma or period attached to a token does not create an extra word. In “Hello, world,” the second token is “world,” including the final comma.
- Assuming every visible gap is an ordinary space. Data copied from a website, PDF, or other system can contain nonbreaking spaces. TRIM does not remove every type of whitespace, so TEXTSPLIT may fail to separate the text as expected. See Troubleshooting for a cleanup option.
- Using a modern function in an older edition. If Excel displays #NAME? for TEXTSPLIT, your edition may not include it. Use the older-version formula for a fixed word position, or use Text to Columns if a one-time split is acceptable.
- Putting the formula inside the source cell. A formula that refers to its own cell creates a circular reference or replaces the original text. Keep the source in one cell, such as A2, and put the result in a different cell, such as C2.
- Expecting punctuation to be removed automatically. The basic formulas return a space-delimited token as it appears. Decide whether punctuation should stay before you use the result for matching, sorting, or reporting.
Tips
- Make the word number easy to understand. Add a heading such as Word number above column B, and use a whole number starting at 1. This helps prevent someone from entering 0 or confusing the position with a character count.
- Use an Excel Table for a growing list. Select the data and choose Insert > Table. A formula entered in a table column normally fills through the rows in that column, and new rows can inherit the formula. Confirm that the source and word-number columns are aligned on each row.
- Keep source text intact. Put the extracted word in a separate column instead of splitting over the original data. That keeps the full phrase available for checking and lets you change the extraction later.
- Check how you define a word in names and codes. A value such as “North-West” has no ordinary space, so it remains a single token. If the relevant separator is a hyphen, comma, slash, or another character, the space-based formula needs to be adapted to that delimiter.
- Remember that TRIM has a specific job. It removes extra ordinary spaces at the beginning and end and reduces repeated ordinary spaces between words. It does not rewrite capitalization, remove punctuation, or reliably clean every unusual character copied from another source.
Troubleshooting
- The formula shows #NAME?. Check whether your Excel version supports TEXTSPLIT. It is available in Microsoft 365 and Excel 2024, but not in some older perpetual versions. Use the legacy formula above for a fixed word position or split the text with Data > Text to Columns. Also check that the function name is spelled TEXTSPLIT.
- The result is blank. The IFERROR version returns a blank when B2 asks for a word number beyond the number of words in A2, or if the formula encounters an error. Check that A2 contains text and B2 contains a positive whole number no larger than the word count. Temporarily removing IFERROR can reveal the underlying error while you investigate.
- The formula returns the wrong word. Count the space-separated tokens in the source cell from left to right. Leading and repeated ordinary spaces are handled by TRIM and the ignore-empty setting, but punctuation remains attached. Check whether a hyphen, line break, or unusual whitespace changes where you expect a boundary to be.
- Excel rejects the formula when you enter it. Some regional settings use semicolons instead of commas between formula arguments. If your Excel uses semicolons, replace the argument-separating commas with semicolons while keeping the quoted space delimiter as a space. Do not remove the quotation marks around that delimiter.
- Copied web text will not split at spaces. A common cause is a nonbreaking space, which looks like an ordinary space but is a different character. For text in A2, you can clean those characters in a helper cell by entering
=SUBSTITUTE(A2,UNICHAR(160)," ")in D2, then use D2 as the source for the extraction formula. Check the cleaned text before filling the formula down; other imported characters may need separate cleanup. - The older formula returns the wrong portion. That formula is designed for a fixed word position and uses a 100-character padding block. If the requested word is longer than that, increase the padding size and update the start offset consistently. If you need to extract different positions on different rows, the TEXTSPLIT formula with a word-number cell is easier to maintain.
Frequently Asked Questions
Can I extract a word by its position in a sentence?
Yes. Put the position number in a separate cell and use the TEXTSPLIT and INDEX formula in the guide. Number words from 1, starting at the left. If the number is larger than the number of space-separated tokens, the IFERROR version returns a blank.
Can I extract the second or third word without TEXTSPLIT?
Yes, but older Excel formulas are less convenient to adapt. The legacy example returns the third word from A2 and has a 100-character limit per word. For a single, one-time split into columns, Data > Text to Columns may be simpler.
Will the formula remove commas and periods?
No. It splits at spaces and returns the token with its punctuation. For example, “apple,” remains “apple,”. If punctuation must be stripped, clean the text separately and verify that removing it will not change meaningful characters in your data.
What if the phrase contains several spaces between words?
The main formula uses TRIM and TEXTSPLIT’s ignore-empty option, so repeated ordinary spaces do not count as empty word positions. The older formula also applies TRIM before splitting its text into padded sections.
Does this work in Excel for Mac or Excel for the web?
The formulas work in the same way when the Excel version includes the functions used. TEXTSPLIT requires Microsoft 365 or Excel 2024. Formula argument separators can vary with regional settings, and the menu layout for Text to Columns may look different across desktop and web versions.
Can the result update when I edit the sentence?
Yes. A formula result recalculates when the source cell changes, and the position can change when you edit the word-number cell. If you paste the result as a value, it no longer updates automatically.
Related
- How to Duplicate an Excel Document on Windows Safely
- How to Duplicate an Excel File on Windows
- How to Duplicate an Excel File Shortcut on Windows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter in Excel to Find the Exact Data You NeedTo filter in Excel, click a cell in your data, then choose Data > Filter; use the arrow…
- Excel LEFT Function: Extract Text from the Left SideTo extract characters from the beginning of a cell in Excel, use the LEFT function. If A2 contains…
- Excel Filter Merged Cells: Why Only the First Row ShowsYes—when a cell is merged vertically across several rows, Excel stores its value only in the top-left cell,…
- Excel Rank Formula: Rank a Number List Highest to LowestTo rank numbers from highest to lowest in Excel, use RANK.EQ with the order argument set to 0.…
- Excel Attendance Sheet With Overtime: Setup and FormulasYes. An Excel attendance sheet can calculate shift hours and overtime from employees’ start times, end times, and…
- Excel MONTH Function: Extract the Month from a DateTo get the month number from a date in Excel, enter =MONTH(A2) in another cell, assuming A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.