Formula Errors

Fix Excel Formula Returning #NAME? Error When Checking Cell Text

Maira Mehtab Sep 20, 2026 868 views

Question details

The user is encountering a #NAME? error when using a formula designed to verify if a cell contains specific text characters, specifically an at sign (@) and a period (.).

Product: Excel
Device & OS: not provided
Scenario: Attempting to validate or check text within a cell for specific components like email address characters.
Observed behavior: The formula fails and returns a #NAME? error instead of correctly evaluating the cell's text content.

Before you start

Double-check your formula for missing double quotation marks around your text characters (like "@"), as omitting these is the most common cause of the #NAME? error.

Solution 1Recommended

Verify the Complete Formula for Syntax Errors

A #NAME? error usually means Excel doesn't recognize a function name or is misinterpreting text as a named range because of missing quotes.

Check function spelling

Click on the cell displaying the #NAME? error and review the formula bar. Ensure all functions (like SEARCH, FIND, or IF) are spelled correctly.

Enclose text strings in quotes

Make sure any specific text you are searching for, such as "@" or ".", is enclosed in double quotation marks. For example, use SEARCH("@", A1) instead of SEARCH(@, A1).

Solution 2

Use the ISTEXT Function to Verify Text Format

If you need to ensure the cell contains text before checking for specific characters, the ISTEXT function provides a clean verification method.

Solution 3

Apply Data Validation to Restrict Text Input

Instead of relying solely on complex formulas to check for specific characters after data is entered, use Data Validation to prevent incorrect input in the first place.

WPS Spreadsheet Solution

Fix Formula Errors and Validate Text Effortlessly with WPS Office

WPS Spreadsheet provides a highly compatible environment for building and debugging formulas. You can easily fix syntax issues causing #NAME? errors, utilize text functions like ISTEXT, and enforce strict data validation rules without hassle.

1. Open your spreadsheet in WPS: Launch WPS Spreadsheet and open the document containing the #NAME? formula error.
2. Utilize Error Checking: Click the warning icon next to the cell with the error and select 'Trace Error' to instantly locate misspelled functions or missing quotes.
3. Set up Data Validation: Navigate to the Data tab, click 'Validation', and easily apply custom formulas to mandate symbols like '@' or '.' for future data entries.

Fully compatible with Microsoft Excel (.xlsx) formulas and formattingBuilt-in error checking traces to quickly identify syntax and typo issuesComprehensive support for advanced text manipulation functionsFree, lightweight, and fast-loading alternative to heavy office suites

microsoft office alternative - wps office

Frequently Asked Questions

What causes the #NAME? error in an Excel formula?

The #NAME? error occurs when Excel cannot recognize text in a formula. This typically happens if a function name is misspelled, if text strings are missing double quotation marks, or if a named range used in the formula doesn't actually exist.

How do I check if a cell contains a specific character like an @ symbol?

You can use a combination of the ISNUMBER and SEARCH functions. Enter the formula =ISNUMBER(SEARCH("@", A1)). This searches for the @ symbol in cell A1 and returns TRUE if it is found.

Why does searching for an @ symbol specifically cause a #NAME? error?

If you input the @ symbol into a formula without surrounding it in double quotes (e.g., using @ instead of "@"), Excel assumes it is a named range or a table reference. Since it doesn't recognize it as plain text, it returns the #NAME? error.

Can I force a user to enter an email address format in a cell?

Yes, you can use Excel's Data Validation feature. Go to Data > Data Validation, select 'Custom', and input a formula that checks for the presence of both an @ symbol and a period to restrict incorrect, non-email entries.

Excel & Data Problems