Excel Error Codes

How to Fix the Excel #N/A Error in IF and TEXTAFTER Formulas

Muhammad Talha Oct 7, 2026 868 views

Question details

The user needs to resolve an #N/A error that occurs when extracting data using the TEXTAFTER function nested within an IF formula.

How to Fix the Excel #N/A Error in IF and TEXTAFTER Formulas

Product: Excel
Device & OS: not provided
Scenario: Attempting to extract text appearing after a specific delimiter character using the TEXTAFTER function alongside an IF logical test.
Observed behavior: The formula returns an #N/A error instead of the extracted text because the target cell does not contain the exact delimiter specified in the formula.

Before you start

Review the source data in your referenced cells (such as E2) to confirm exactly how the text is formatted, paying close attention to spaces around your target delimiters.

Solution 1Recommended

Check and Update the Delimiter in the TEXTAFTER Formula

The most common cause of the #N/A error in TEXTAFTER is a mismatch between the delimiter in your formula and the actual text in the cell. Correcting the delimiter resolves the issue instantly.

The TEXTAFTER function requires an exact match for the delimiter you provide. For example, if your formula looks for a colon followed by a space (": "), but the cell only contains a colon (":"), the function will fail and output #N/A.

Select the error cell

Click on the cell displaying the #N/A error to reveal its contents in the top formula bar.

Inspect the delimiter argument

Locate the TEXTAFTER portion of your formula and identify the delimiter being used (e.g., ": ").

Verify against the source cell

Look at the referenced source cell (e.g., 'Originating BU Check'!E2). If there is no space after the colon, remove the space in your formula so the delimiter reads ":" instead.

Apply the new formula

Press Enter to apply the updated formula. The #N/A error should disappear, and the correct text will be extracted.

Check and Update the Delimiter in the TEXTAFTER Formula

Formula Adjusted: Adjusting the delimiter from a colon with a space to just a colon resolves the strict matching requirement of the TEXTAFTER function.

Solution 2

Use IFERROR to Handle Missing Delimiters Gracefully

Wrap your formula in an IFERROR function to prevent #N/A errors from displaying when a cell legitimately does not contain the specified delimiter.

Efficient Spreadsheet Management

Easily Manage Formulas and Avoid Errors with WPS Spreadsheet

WPS Office offers a powerful, free spreadsheet application that fully supports advanced data extraction formulas. Easily troubleshoot formula errors with an intuitive interface, syntax highlighting, and seamless compatibility with Microsoft Excel formats.

1. Open your file in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open your existing .xlsx document containing the data.
2. Select the target cell: Click on the cell where you want to display the extracted text.
3. Enter a text extraction formula: If TEXTAFTER isn't available, type a highly compatible alternative formula such as =IFERROR(RIGHT(E2, LEN(E2)-FIND(":", E2)), "") to extract text after a colon.
4. Apply across rows: Press Enter to see the result, then click and drag the fill handle at the bottom-right of the cell to apply the formula to the rest of your column.

100% compatibility with Microsoft Excel formulas and .xlsx file formatsIntuitive formula builder and syntax highlighting to spot delimiter errors quicklyFree to use with a lightweight, fast installation processCross-platform support for Windows, Mac, Linux, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TEXTAFTER function return an #N/A error?

The TEXTAFTER function returns an #N/A error when the delimiter you specified in the formula cannot be found in the target cell. Ensuring an exact match, including spaces, is crucial for the formula to work.

How can I extract text after a character if TEXTAFTER isn't available in my software?

If your spreadsheet version does not support the newer TEXTAFTER function, you can use a combination of RIGHT, LEN, and FIND functions. For example: =RIGHT(A1, LEN(A1)-FIND(":", A1)) will extract everything after the first colon.

Can I search for multiple different delimiters at once using TEXTAFTER?

Yes, TEXTAFTER allows you to specify an array of delimiters by enclosing them in curly brackets. For example, using TEXTAFTER(A1, {":", "-"}) will extract text after either a colon or a hyphen, whichever appears first.

How do I make the TEXTAFTER function ignore case sensitivity?

By default, the TEXTAFTER function is case-sensitive. You can change this behavior by setting the match_mode argument to 1, which makes it case-insensitive. For example: =TEXTAFTER(A1, "Delimiter", , 1).