Formula Errors

How to Fix Excel #VALUE! Error When Searching for Literal Text /*

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to find a way to search for the literal text string '/*' in Excel without triggering incorrect results or a #VALUE! error.

Product: Excel
Device & OS: not provided
Scenario: Using search functions to locate a specific string that contains an asterisk (such as '/*') within a cell or dataset.
Observed behavior: Excel interprets the asterisk as a wildcard rather than literal text, leading to inaccurate matches or #VALUE! errors when the search result is passed to nested functions like INDEX.

Before you start

Verify which text-matching function you are currently using (e.g., COUNTIF, SEARCH, or FIND) to determine whether it inherently treats asterisks as wildcard characters.

Solution 1Recommended

Use a Tilde (~) to Escape the Asterisk Wildcard

This is the best method if you want to continue using wildcard-supporting functions like COUNTIF or SEARCH while treating the asterisk as a literal character.

By default, Excel treats the asterisk (*) as a wildcard that represents any number of characters. Prefixing the asterisk with a tilde (~) forces Excel to read it literally.

Select the target cell

Click on the cell where you want to enter or correct your search formula.

Modify the search criteria

In your COUNTIF or SEARCH formula, place a tilde (~) directly before the asterisk. For example, to search for '/*', change your criteria string to "/*/~**".

Apply the formula

Press Enter. The function will now accurately locate or count the literal string without treating the asterisk as a wildcard.

Multiple Wildcards: If you are trying to find '/*' surrounded by any other text, the formula =COUNTIF(A1, "*/*~**") uses the outer asterisks as standard wildcards and the inner '~*' as the literal asterisk.

Solution 2

Switch to the FIND Function

Use the FIND function as an alternative, since it does not support wildcards and inherently treats asterisks as literal characters.

Solution 3

Isolate Nested Functions to Fix #VALUE! Errors

Sometimes the #VALUE! error originates from a surrounding function (like INDEX) rather than the search function itself.

Resolve formula errors easily

Handle Complex Search Formulas Easily in WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced data lookup and text extraction formulas, including exact literal matches and wildcard handling. It is the perfect environment for troubleshooting complex datasets efficiently.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing the problematic formula.
2. Locate the formula: Click on the cell returning the #VALUE! error or incorrect calculation.
3. Use Error Checking: Navigate to the 'Formulas' tab and click 'Error Checking' to evaluate the formula step-by-step and see exactly where it fails.
4. Apply literal text adjustments: Edit your formula in the formula bar, applying either the tilde (~) escape character or switching to the FIND function to fix the literal asterisk search.

100% compatible with Microsoft Excel functions like COUNTIF, SEARCH, FIND, and INDEX.Built-in error tracing tools help easily identify the root cause of #VALUE! errors in nested formulas.Lightweight software architecture ensures fast calculation even on large spreadsheets.Completely free to download with an intuitive, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel return incorrect results when searching for an asterisk?

In Excel, the asterisk (*) is a wildcard character that represents any sequence of characters. When you search for it directly using functions like SEARCH or COUNTIF, Excel attempts to match any text string instead of looking for the actual asterisk symbol, leading to false matches.

What is the difference between FIND and SEARCH in Excel formulas?

The FIND function is case-sensitive and does not support wildcards, meaning it treats characters like asterisks (*) and question marks (?) literally. The SEARCH function is not case-sensitive and supports wildcards, meaning those characters must be explicitly escaped with a tilde (~) to be found literally.

How do I search for a literal question mark (?) in my Excel data?

Like the asterisk, the question mark is a wildcard that stands for a single character. To search for a literal question mark using COUNTIF or SEARCH, prefix it with a tilde (~). For example, use "~?" in your search criteria.

Excel & Data Problems