To extract everything after a word in Excel for Microsoft 365, put the source text in A2 and enter =TRIM(TEXTAFTER(A2,"Status")) in another cell. If A2 contains Status Complete – reviewed, the result is Complete – reviewed. TEXTAFTER is available in Microsoft 365 and Excel 2024; if you have an older version, use the compatible MID and FIND method below. First decide whether you want all text after the first match, after a particular occurrence, or just the next word.
What You’ll Need
- A workbook with the text you want to split. The examples use the source text in A2 and place the result in B2.
- The exact word or phrase that marks where the extraction should begin. Check whether punctuation or spaces are part of the delimiter.
- Microsoft 365 or Excel 2024 for TEXTAFTER. For older Excel versions, use the alternative formula in Step 5.
- A spare output column so the formula does not overwrite your original data. If your data is important, save a copy before making large changes.
Step-by-Step
- Check a few source cells. Find a representative example and identify the word that comes before the content you need. For instance, a cell might contain Status Complete – reviewed, and you want everything after Status. Note whether the word appears more than once, whether some rows have extra spaces, and whether punctuation follows it. These details affect the delimiter and the result. Do not edit the original column while you are still checking the formula.
- Choose what counts as after the word. TEXTAFTER removes the delimiter itself. If you use Status as the delimiter in Status Complete – reviewed, the remaining text begins with a space. TRIM removes leading and trailing ordinary spaces, so the result is Complete – reviewed. If the delimiter is a word that could occur inside a longer word, use a boundary such as a space or punctuation too. For example, if entries consistently have a space after Status, specifying Status followed by a space avoids matching Status inside Statuses.
- Enter the Microsoft 365 formula. With the text in A2 and the word Status marking the start point, enter this in B2:
=TRIM(TEXTAFTER(A2,"Status"))
For the example Status Complete – reviewed in A2, B2 returns Complete – reviewed. Press Enter. In current Microsoft 365 versions, the formula may fill down automatically if the range is an Excel Table. Otherwise, select B2 and drag its fill handle down the column, or double-click the fill handle to copy the formula alongside neighboring data. Confirm the first few results before using them elsewhere.- Use a longer delimiter when needed. If the source follows a consistent label format, include its separator in the delimiter. With the entry Status: Complete – reviewed in A2, and with the colon and following space always present, use:
=TEXTAFTER(A2,"Status: ")
This returns Complete – reviewed without needing to trim the leading space. Including punctuation or a following space can help distinguish a standalone label from the same letters inside another word. Do not add a separator that is missing from some rows unless you are prepared to handle those rows as exceptions.- Choose a later occurrence if the word repeats. TEXTAFTER normally uses the first match. Its optional third argument chooses which occurrence to use. With text in A2 such as Status Draft; Status Approved, this formula returns Approved:
=TRIM(TEXTAFTER(A2,"Status",2))
The 2 means use the second occurrence. A negative occurrence number counts from the end; for example, -1 selects the last match. Check the source carefully before using an occurrence number, because rows with fewer matches may return an error unless you supply a fallback or handle the error.- Fill the formula down and review the results. Copy B2 through the rows that contain source text. Compare results with the original cells, particularly the first and last row and entries with repeated words, punctuation, or unusual spacing. If the extracted text looks correct, you can leave the formulas in place or copy the result cells and use Home > Paste > Paste Values to replace them with fixed text. Keep the original data until you have verified the output.
- Use a formula for older Excel versions if TEXTAFTER is unavailable. With the text in A2 and the delimiter Status, enter this in B2:
=TRIM(MID(A2,FIND("Status",A2)+LEN("Status"),LEN(A2)))
For Status Complete – reviewed, the result is Complete – reviewed. FIND locates the word, LEN counts its characters, and MID returns the characters after it. This approach works in older desktop Excel versions too, but FIND returns an error if the delimiter is absent. The formula is case-insensitive, like TEXTAFTER’s default matching behavior.
Common Mistakes
Including the word in the result: TEXTAFTER returns characters after the complete delimiter, not the delimiter itself. If the target seems to remain, check that you are viewing the result cell rather than the source, and make sure the formula delimiter matches the word you intended to remove.
Leaving an unwanted leading space: When the delimiter is only the word, a space immediately after it stays in the result. Wrap the formula in TRIM to remove leading and trailing ordinary spaces. TRIM also reduces repeated internal ordinary spaces to one, so do not use it if multiple spaces inside the extracted text are meaningful. It does not remove every unusual whitespace character, such as a nonbreaking space.
Matching part of a different word: Searching for Art can match the letters in Article. If that is a risk, use a delimiter with a reliable boundary, such as a space after the word or the exact label and punctuation. A boundary must match the actual data; there is no single delimiter that fits every inconsistent text pattern.
Using the first match by accident: The default is the first occurrence, which may not be the one you want when a label appears more than once. Set the occurrence number deliberately and verify that the relevant rows contain that many matches.
Expecting a formula to change the source cell: These formulas return an extracted value in a separate cell. They do not remove text from the original entry. Keep a separate result column until you have checked the output, and use Paste Values only when you are ready to replace formulas.
Tips
- Start with one or two examples before filling hundreds of rows. Include a normal entry and one that represents an edge case, such as a missing word or an extra separator.
- For data with a label and punctuation, use the whole label as the delimiter, such as Status: followed by a space. This can make the extracted result cleaner and reduce accidental partial-word matches.
- TEXTAFTER is not case-sensitive by default. If the capitalization varies, it will still find the delimiter. If capitalization is important, TEXTAFTER has a match_mode option, but use it only when you have a specific reason to distinguish cases.
- For a table with a header such as Extracted text, Excel can copy a formula down as a calculated column when you enter it in the first data row. Check that behavior in your workbook; a regular range may require you to fill the formula down yourself.
- When you need the extracted result for another application, copy the results and choose Home > Paste > Paste Values. This keeps the text while removing the formulas’ dependency on the original cells.
- Excel’s formula argument separator can vary with regional settings. The examples use commas as requested for standard English-language syntax. If Excel reports a formula-entry error in a locale configured for semicolons, replace argument-separating commas with semicolons in the formula bar.
Troubleshooting
You see #N/A from TEXTAFTER: Excel could not find the delimiter in that cell. Check spelling, punctuation, and spaces, including whether the word has a colon or other mark beside it. If missing matches should return a blank, provide TEXTAFTER’s optional if_not_found argument. With source text in A2 and Status as the delimiter, this version returns an empty string when the word is not found:
=TRIM(TEXTAFTER(A2,"Status",1,0,0,""))
The arguments after the delimiter specify the first occurrence, default case-insensitive matching, no special end-of-text match, and a blank fallback. If you prefer to flag missing labels, use a message such as Not found instead of an empty string.
You see #VALUE! with the older formula: FIND did not find the delimiter, or the formula refers to the wrong cell. Confirm that the source text is actually in A2 and the delimiter appears in it. You can suppress missing-word errors with IFERROR. With text in A2 and Status as the target, enter:
=IFERROR(TRIM(MID(A2,FIND("Status",A2)+LEN("Status"),LEN(A2))),"")
This returns a blank when FIND cannot locate Status. Use a visible fallback such as Not found if blanks could be mistaken for valid empty results.
The result still has odd spaces: TRIM handles ordinary spaces but not every hidden character copied from websites or exported reports. If entries contain nonbreaking spaces, you may need to clean the source text first. You can test a copied example by inspecting the cell and checking whether the apparent gap behaves like a normal keyboard space. Avoid replacing characters throughout the data until you know what the character is and whether it appears in meaningful text.
Excel displays the formula instead of a result: The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, then re-enter the formula. On Windows, Ctrl+` toggles Show Formulas; on Mac, use Control+` where available or check Formulas > Show Formulas. Also make sure the formula begins with an equals sign and was not entered with an apostrophe before it.
Frequently Asked Questions
Can Excel extract only the next word after a target word?
TEXTAFTER returns all text after the delimiter, not just the next word. In Microsoft 365 or Excel 2024, you can wrap it in TEXTBEFORE to stop at the next space. With text in A2 such as Status Complete – reviewed, this returns Complete:
=TEXTBEFORE(TRIM(TEXTAFTER(A2,"Status"))," ")
TEXTBEFORE is available in Microsoft 365 and Excel 2024. If the next item may include punctuation or spaces, adjust the stopping delimiter to suit the text format.
Can I extract text after the last occurrence of a word?
Yes. TEXTAFTER accepts a negative occurrence number to count from the end. With source text in A2 and the target Status, use =TRIM(TEXTAFTER(A2,"Status",-1)). This returns everything after the last match. Make sure the target appears in the rows where you expect it.
Does TEXTAFTER work in Excel 2021?
No. TEXTAFTER is available in Microsoft 365 and Excel 2024, not Excel 2021. Use the MID, FIND, and LEN formula in Step 5 for Excel 2021 and older versions. If you are unsure which version you have, check File > Account in Excel for Windows.
Can I extract after a phrase instead of one word?
Yes. Use the complete phrase as the delimiter, including spaces or punctuation when they consistently appear. For example, with data in A2 that contains Order number: 4821, =TEXTAFTER(A2,"Order number: ") returns 4821. The formula will not find the phrase if the source uses a different spelling or separator.
Can I keep the extracted data when I delete the source column?
Yes, but first convert the formula results to fixed values. Copy the result range, then use Home > Paste > Paste Values in the destination. Verify the values before deleting the original column, because formulas may otherwise refer to cells that no longer contain the source text.
Related
- How to Drag Serial Numbers in Excel: Fill a Series
- Word Wrap in Excel: How to Make Text Wrap in Cells
- How to Do Subscript in Excel With a Shortcut
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Examples: Formulas for Everyday Excel TasksTo return a value for a matching ID, use XLOOKUP with the value to find, the lookup range,…
- Excel Spreadsheet Sort by Highest Number: Steps & TipsTo sort an Excel spreadsheet by its highest number, click a cell in the number column and choose…
- Excel Formula for List of Unique Values: 10 Ways That WorkFor a basic list of distinct entries in Microsoft 365, with source values in A2:A20, enter =UNIQUE(A2:A20) in…
- Excel Date Filter Multiple Criteria: Practical MethodsTo filter dates in Excel by multiple criteria, turn on AutoFilter, open the date column’s dropdown, and choose…
- DATEDIF Spreadsheet Examples: Years, Months, and DaysIn Excel for Microsoft 365, use DATEDIF(start_date,end_date,unit) to calculate the elapsed time between two dates in completed years,…
- Round Up in Excel: Use ROUNDUP for Any Number or DecimalTo round a value in Excel away from zero, use =ROUNDUP(A2,0) when the number is in cell A2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.