OmniDocExcel FIND and SEARCH function usage and #VALUE! mistake
Explain the difference between the FIND and SEARCH functions in Excel: FIND is case-sensitive and does not support wildcards, while SEARCH is different. Then troubleshoot the #VALUE! error in the order of easy first and then difficult, and give the method of using SEARCH to search for records by keywords.
Key points of this article
- FIND is case-sensitive and does not support wildcards. SEARCH is case-insensitive and supports * and ?. Both return the position number of the text.
- Both return #VALUE! when the text cannot be found, so first determine whether it is not found or there is a problem with the case, space, and position parameters.
- For the same value, SEARCH can be used to calculate numbers, but FIND reports an error. Usually the case is inconsistent. You can directly determine the reason.
- Use ISNUMBER(SEARCH(...)) to determine whether a cell contains keywords, and combined with filtering, you can quickly find records that meet the conditions.
- When enclosing IFERROR or ISNUMBER in a formula, the cause must be confirmed first, otherwise all errors will be hidden.
When making Excel tables, many people use the FIND function for the first time to split numbers, extract prefixes or search for keywords, but #VALUE! errors suddenly appear in the cells. Obviously I feel that the word is indeed in the table, but the function cannot find it. Another similar function SEARCH can sometimes calculate the result, but sometimes reports an error, so I wonder if the wrong function is used.
This article is written for people who often use Excel to split, extract, and filter text, such as readers who organize customer lists, take prefixes from order numbers, and find records by keywords. After reading, you will be able to distinguish the difference between FIND and SEARCH, know the common causes of #VALUE!, and eliminate them one by one in order.
Should I use FIND or SEARCH to search text? One sentence conclusion
One sentence conclusion: Use FIND when case sensitivity is required or what you are looking for is * and ? itself; use SEARCH when case does not matter or fuzzy matching is required. The syntax of both is the same, both are "the text to be found, the text to be searched for, [starting position]".
| your situation | Recommended practices | reason |
|---|---|---|
| Models AB and ab need to be distinguished | Use FIND | FIND is strictly case sensitive |
| I just want to know whether the keyword is there, regardless of capitalization. | Use SEARCH | SEARCH is not case sensitive |
| Use * or ? for fuzzy matching | Use SEARCH | SEARCH supports wildcard characters, FIND will treat them as ordinary characters |
| What you are looking for is exactly * or ? itself | Use FIND; or write ~* or ~? in SEARCH | Wildcard characters have special meaning in SEARCH and need to be escaped |
| Just want to determine whether it contains | Outer cover ISNUMBER | If a number is found, an error is returned. If not found, an error is returned. ISNUMBER is converted to TRUE or FALSE. |
First, determine which type of problem it is
#VALUE! It only means the function did not get a valid result, without directly telling you the reason. First, compare with the table below to narrow down the inspection scope.
| Symptoms | Common causes | How to confirm this |
|---|---|---|
| Text visible to the naked eye, FIND Return #VALUE! | Case inconsistency | Replace FIND with SEARCH and calculate again; if you can get a number, it's a case issue |
| The result is yes, but the extracted content is either too many or fewer characters | Cells contain invisible spaces or full-width symbols | Calculate the number of characters using LEN and compare it to the number of characters visible to the naked eye |
| Searching for words from another cell always causes errors | The referenced cells are empty, or the content contains only spaces | Open the editing bar of the cell to see if it actually contains content |
| After writing the third parameter, an error occurs | The starting position is less than 1 or greater than the text length | Check if start_num is between 1 and text length |
| When pulling down the entire column, only a few rows report errors | These lines do not contain the text, or the original cell itself is an error value | Check the original content separately for these lines |
Inspect and fix each item one by one
Follow the steps below to check; the earlier steps are easier to do and can rule out most situations.
- First, check how the function is written. Both FIND and SEARCH require two required parameters: the text to be found and the cell being searched for. The order must not be reversed, for example, =FIND("-", A2) instead of =FIND(A2, "-").
- Try replacing FIND with SEARCH. If SEARCH returns numbers, it means the problem lies with the case case. If case-sensitive is necessary, keep FIND and unify both sides in the formula with LOWER or UPPER (for example, =FIND(LOWER("ab"), LOWER(A2)). This way, the original data is not altered and the calculation works normally.
- Clean up hidden characters. Use LEN to count the number of characters in a cell and compare it with the number of characters you see. Regular spaces can be removed with TRIM; Data pasted from web pages or other systems often contains non-line spaces and can be removed with =SUBSTITUTE(A2,CHAR(160),""). Full-width commas, horizontal lines, and other symbols can also cause them to be lost; you can use SUBSTITUTE to replace them with half-width and then search.
- Check the starting position. If the third argument is omitted, the search starts from the first character by default. If you write a 0, negative number, or a number longer than the text length, it will return #VALUE!.
- Confirm that the search term has content. If the search term references another cell, open it to confirm it is not blank and there are no extra spaces.
- Finally, use IFERROR or ISNUMBER to handle the missing situation, for example: =IFERROR(FIND("-", A2), "None"), or =IF(ISNUMBER(SEARCH("REFUND", A2)), "Yes", "No").
- After finding the position, use LEFT and MID to extract the required text. For example, =LEFT(A2,FIND("-",A2)-1) can extract the content before the first blank.
Use SEARCH to find records in a column
If your goal is to find all rows containing a certain keyword, you can do it as follows without checking line by line.
- In the second line of the blank column, enter =IF(ISNUMBER(SEARCH("KEYWORDS",A2)),"Hit",")), then fill in the blank below.
- Use filtering on this column to display only the row with "hits," so you can see all records that meet the criteria.
- If you only need the position of the first hit record, you can use =MATCH(TRUE,INDEX(ISNUMBER(SEARCH("KEYWORD",A2:A100)),0),0). It returns the row number within the range, not the row number of the worksheet; If it returns #N/A, it means there are no hits in this area.
How to Prevent It from Happening Next Time
- After pasting the data, first randomly check the LEN results of several cells to confirm no hidden characters before starting to write the formula.
- For content copied from web pages, chat apps, or PDFs, it is recommended to paste it into a plain text editor to remove formatting before pasting it into Excel.
- Try to use a single function uniformly for the same table. When case sensitivity is needed, use LOWER or UPPER for unified handling to avoid mixing FIND and SEARCH and causing inconsistent results.
- When adding IFERROR or ISNUMBER to the formula's outer layer, leave a clear display such as "Not found" to facilitate later filtering and checking.
- For important tables, randomly select a few rows to manually check the formula results, especially for data with inconsistent lengths or complex sources.
❓ Frequently Asked Questions (FAQ)
1. What do the numbers returned by FIND and SEARCH mean?
The number indicates the position of the search text in the text being searched for, counting from 1. For example, in "AB-123", look for "-", FIND returns 3. If you can't find it, both return #VALUE!.
2. Using SEARCH or reporting #VALUE!, could there be a problem with the function?
The function itself is usually fine. Common reasons include the text not actually existing, or the starting position is out of range, or there may also be invisible characters. First, use LEN to check the cell contents, then verify the parameters.
3. Can FIND be searched using wildcards?
No. FIND will combine * and ? Treat as regular characters. If fuzzy matching is needed, SEARCH should be used.
4. Will IFERROR also hide other errors?
Yes. IFERROR overrides all error types, including errors caused by incorrect parameter writing. Therefore, before applying, you should first confirm that the formula itself is correct before deciding whether to use it to handle the missing situation.
📝 Summary
The core difference between FIND and SEARCH lies in whether it is case-sensitive and whether wildcards are supported. When encountering the Excel FIND function returning #VALUE! first use SEARCH to check, then check hidden characters and starting positions, and finally use IFERROR or ISNUMBER to handle missing cases.
