To return the text to the right of a character in Excel, use TEXTAFTER in Microsoft 365 or Excel 2024, such as =TEXTAFTER(A2,"|") when the text is in A2 and the separator is a vertical bar. In older Excel versions, use =RIGHT(A2,LEN(A2)-FIND("|",A2)) to get the same result.
What “text right of a character” means
Suppose a cell contains Order|4837. The character | separates two pieces of text: Order is on its left, and 4837 is on its right. A formula that returns text right of the character returns the part after the separator, not the separator itself. This is useful for splitting imported data, extracting a code from a label, or separating a user name from a domain.
The character you search for is often called a delimiter. It can be a comma, hyphen, space, slash, colon, underscore, or a longer string. For example, a file path might use a backslash, while an email address uses the at sign. Excel does not need to treat the delimiter as punctuation; it simply looks for the specified text within the cell.
Before choosing a formula, decide which occurrence matters. A cell may have one separator, several separators, or none. You may want everything after the first occurrence, after the second, or after the last. Those are different extraction rules, and the formula should match the one you intend.
Use TEXTAFTER in Microsoft 365 or Excel 2024
TEXTAFTER is the clearest option when your Excel version includes it. It returns the text after a specified delimiter and leaves the delimiter out of the result. With text in A2 and a vertical bar as the separator, enter this in another cell:
With the source text in A2, containing Order|4837, and the delimiter |, use:
=TEXTAFTER(A2,"|")
The result is 4837. Enter the formula in B2, then use the fill handle or copy and paste to apply it to other rows. The function is available in Microsoft 365 and Excel 2024. If Excel displays #NAME?, your version may not include the function; use the compatible formula in the next section instead.
The basic formula returns text after the first matching delimiter. You can change which occurrence it uses with the optional third argument, instance_num. For example, if A2 contains North|Retail|4837, the following formula returns 4837, the text after the second vertical bar:
With North|Retail|4837 in A2 and | as the delimiter, use:
=TEXTAFTER(A2,"|",2)
For the same cell, use a negative occurrence number to count from the right. This returns the text after the final vertical bar, which is again 4837 in this example:
With North|Retail|4837 in A2, use:
=TEXTAFTER(A2,"|",-1)
This distinction matters when the number of separators varies between rows. If your rule is “take the final segment,” specifying -1 is more reliable than assuming that every row has exactly two separators.
Use RIGHT, LEN, and FIND in older Excel
In versions without TEXTAFTER, combine three established functions. FIND locates the delimiter’s position, LEN counts the total characters, and RIGHT returns a specified number of characters from the end. Subtracting the delimiter position from the total length gives the number of characters after it.
With Order|4837 in A2 and | as the delimiter, enter:
=RIGHT(A2,LEN(A2)-FIND("|",A2))
The result is 4837. For instance, if A2 has 11 characters and the bar is in position 6, the text after it has 11 minus 6, or 5, characters. RIGHT returns those final five characters. This formula works in older desktop versions as well as current Excel, so it is a practical choice when a workbook must be shared with people using different releases.
For a multi-character delimiter, enter the full delimiter in both approaches. For example, with INV::4837 in A2, this formula returns 4837:
With INV::4837 in A2, use:
=RIGHT(A2,LEN(A2)-FIND("::",A2))
FIND returns the starting position of the two-character delimiter. Subtracting that position leaves the count of characters after the whole delimiter, so the formula does not mistakenly include either colon.
Choose the right occurrence and matching behavior
The basic formulas assume you want text after the first delimiter. In a value such as dept:team:employee, that means the result is team:employee, not just employee. Use TEXTAFTER with a positive occurrence number to select a later delimiter, or -1 for the last delimiter. The older RIGHT/LEN/FIND pattern finds the first match; it does not automatically select the last one.
Matching can also depend on letter case. FIND is case-sensitive, so searching for Q does not match a lowercase q. TEXTAFTER is case-insensitive by default. To make its matching case-sensitive, set the fourth argument, match_mode, to 1. With keyQvalue in A2, this formula finds the uppercase Q and returns value:
With keyQvalue in A2, use:
=TEXTAFTER(A2,"Q",1,1)
Check the case behavior if the delimiter is a letter rather than punctuation. For punctuation separators such as a slash or pipe, case sensitivity is usually irrelevant.
Handle missing delimiters and blank results
If the delimiter is absent, the basic TEXTAFTER and FIND formulas return an error because Excel cannot locate the requested match. That may be helpful while checking data quality, but a visible error is not always suitable in a finished report. In Microsoft 365 or Excel 2024, TEXTAFTER has an optional fourth argument called if_not_found. Set it to an empty string to return a blank instead:
With source text in A2 and | as the expected delimiter, use:
=TEXTAFTER(A2,"|",1,0,"")
This formula returns the text after the first bar when one exists and a blank when it does not. The fourth argument here is if_not_found; the fifth is match_mode. To avoid confusion, note that the arguments between the delimiter and if_not_found must still be supplied in their correct positions.
For an older Excel version, wrap the original expression in IFERROR. With the same A2 layout, use:
=IFERROR(RIGHT(A2,LEN(A2)-FIND("|",A2)),"")
This returns a blank if the delimiter cannot be found. It also suppresses other errors from the expression, so use it when a blank is an acceptable fallback. If you need to distinguish a genuinely empty result from a malformed row, leaving the error visible or displaying a message such as Missing separator may be more informative. A cell ending in the delimiter, such as Order|, correctly produces an empty result because there is no text after it.
Apply the formula to a column of data
For a list, put the extraction formula beside the first source value and fill it down. If source values are in A2:A100, enter the appropriate formula in B2, then drag the fill handle at the lower-right corner of B2 down to B100. Excel adjusts the row reference automatically, changing A2 to A3, A4, and so on.
- Keep the original column until you have checked the extracted results.
- Look at examples with no delimiter, multiple delimiters, and a delimiter at the beginning or end.
- If you need fixed results rather than formulas, copy the output cells and use Home > Paste > Paste Values.
- For a formatted Excel table, entering the formula in the first row of a new table column usually fills the calculated column automatically.
Formulas recalculate when their source cells change, while pasted values do not. Keep formulas if the source data will be updated and the extracted text should follow those updates. Use values if the extraction is a one-time cleanup and you want to remove the formula dependency.
Common problems and practical checks
A formula can be syntactically correct and still return an unexpected result because the source data is inconsistent. Imported text may contain spaces, a different separator, or an invisible character. Check the actual contents of a sample cell in the formula bar, and compare rows that work with rows that fail. A hyphen and an en dash look similar but are different characters; a regular space and a nonbreaking space are different too.
#N/Aor an error: The delimiter may be missing, or the formula may be searching for a different character. Confirm the exact separator and decide whether to show an error, a blank, or a message.- Too much text is returned: The formula probably found the first delimiter when you intended the last or a later one. For supported Excel versions, choose the appropriate
instance_numinTEXTAFTER. - The separator remains in the result: Check whether your formula is actually extracting after the separator. The formulas shown here exclude it; a different formula or a mistaken delimiter position may be responsible.
- The result appears blank: The delimiter may be the final character, or an error-handling formula may be hiding a missing-delimiter error. Inspect the source cell before assuming the formula removed valid text.
Also distinguish text extraction from numeric conversion. If the returned characters look like a number, Excel may still treat them as text, especially when they contain leading zeros. That is often desirable for identifiers such as 00483, where converting to a number would remove the zeros. Convert only when you need to calculate with the result and the value is genuinely numeric.
Alternatives to a formula
If you need to split a whole column once rather than maintain live formulas, Excel’s Text to Columns tool can separate values using a delimiter. Select the source range, then choose Data > Text to Columns, select Delimited, choose or specify the separator, and complete the wizard. This process writes the split results into neighboring columns, so check that those columns are empty or that you have a backup before proceeding.
For repeatable imports or more involved cleanup, Power Query can split a column by a delimiter during data transformation. In Excel for Windows, you can access it through Data > From Table/Range for a table, then use the split-column options in the Power Query Editor. This is useful when new data will be loaded repeatedly and the same transformation should be reapplied. Unlike a simple cell formula, Text to Columns changes the selected data, and Power Query requires refreshing the query to load updated results.
Use a formula when you want an immediately updated result beside each source value. Use Text to Columns for a quick one-time split, and consider Power Query for a repeatable import workflow. These methods solve related problems, but they differ in whether the result updates automatically and how much of the workbook’s data structure they change.
Frequently Asked Questions
What is the simplest formula to get text after a delimiter?
In Microsoft 365 or Excel 2024, use =TEXTAFTER(A2,"|"), replacing A2 and the bar with your cell and delimiter. It returns everything after the first matching delimiter.
How do I get text after the last character or separator?
Use =TEXTAFTER(A2,"|",-1) to return text after the final vertical bar. Replace the bar with your actual delimiter. This function requires Microsoft 365 or Excel 2024.
How can I do this without TEXTAFTER?
Use =RIGHT(A2,LEN(A2)-FIND("|",A2)). It works in older Excel versions when the delimiter is present and returns the text after its first occurrence.
Does TEXTAFTER include the delimiter in its result?
No. It returns the text after the delimiter, excluding the delimiter itself. If the source ends with the delimiter, the result is an empty string.
Can I return blank when the delimiter is missing?
Yes. In supported Excel versions, supply an empty string as the if_not_found argument to TEXTAFTER. For the older RIGHT formula, wrap it in IFERROR and return "".
Related
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Percentage in Excel: Formulas and Table TipsTo calculate a percentage in Excel, divide the part by the total, then format the result as a…
- Replace Text in Excel: Find, Replace, and FormulasTo replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and…
- Excel Time Converter: Formulas, Formats, and TipsTo convert decimal hours in Excel into a time value, divide by 24; to convert an Excel time…
- Excel NOW Function Not Updating? Causes and FixesExcel’s NOW() function does update, but it does not run like a live clock: it returns the current…
- VLOOKUP Excel Example: Look Up Data Across Two SheetsTo look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in…
- Sequence Excel Column: Create Numbers with SEQUENCETo create a sequence of numbers down an Excel column, enter =SEQUENCE(10) in the first cell; in Microsoft…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.