If you searched for find text within string excel, you are looking for a way to check whether a particular word, code, or set of characters appears inside a longer piece of text in a spreadsheet cell, and ideally to find out where it sits. Excel does not have a single button labelled with that phrase, but it does have a small set of functions built for exactly this job. The most important are FIND and SEARCH, supported by ISNUMBER, IF, and COUNTIF for turning the result into a clear yes-or-no answer. This guide explains each method step by step, with examples you can copy and adapt, so you can locate text inside cells without scanning long lists by eye.
The phrase itself is worth unpacking. In spreadsheet language, a “string” simply means a piece of text: a product code, a name, a sentence, or any characters stored in a cell. To “find text within a string” therefore means to search inside that text for a shorter piece of text, called the substring. You might want to know whether an order code contains “EXP”, whether a description mentions “refurbished”, or whether a list of email-style addresses is even relevant. The techniques below work for all of those cases, and they work without changing your original data.
What ‘find text within string excel’ Means
In Excel, “find text within string excel” describes a formula task, not a menu command. You give Excel two pieces of information: the text you are looking for, and the cell that contains the longer text. Excel then either returns the position of the match, returns an error when there is no match, or, with a small addition, returns a friendly answer such as “Yes” or “No”.
Position means the character number at which the match starts. If the cell contains “Order-12345” and you search for “123”, Excel reports position 7, because the digits start at the seventh character. Counting starts at 1 for the first character, and every character counts, including letters, digits, spaces, and punctuation such as hyphens. That position number is useful in its own right, because it lets you extract the characters before or after the match using other functions such as LEFT, RIGHT, or MID.
There is one behaviour that surprises beginners: when Excel cannot find the text, it does not return zero. It returns an error, shown as #VALUE!. That error is not a fault in your spreadsheet. It is Excel’s way of saying “no match”. Most practical formulas therefore wrap the search in ISNUMBER or IFERROR so that a missing match becomes a tidy result instead of an error message.
Before using formulas, it is worth knowing that Excel also has a manual Find tool, opened with Ctrl plus F on Windows. That tool is excellent for locating a value once, in a sheet you are looking at. Formulas are better when you need to check hundreds or thousands of rows, repeat the check, or build a result that other formulas can use. This guide focuses on formulas for that reason, while noting the manual tool as a quick alternative.
How to ‘find text within string excel’ With FIND and SEARCH
The two core functions are FIND and SEARCH. They look similar, and for many tasks they give the same answer, but they differ in one important respect: case sensitivity.
FIND is case-sensitive. It treats uppercase and lowercase letters as different characters. Searching for “exp” in “EXP-4421” with FIND will fail, because the cases do not match. SEARCH is not case-sensitive. The same search with SEARCH will succeed, because it ignores the difference between uppercase and lowercase. SEARCH also allows wildcard characters, which FIND does not.
The syntax for both is the same. You write the text to find first, then the cell or text to search in, and optionally a starting position. For example, to search cell A2 for the code “EXP”, you would write =FIND(“EXP”, A2) or =SEARCH(“EXP”, A2). If A2 contains “EXP-4421”, both return 1, because the match starts at the first character. If A2 contains “Order EXP-4421”, both return 7, because the match starts after “Order ”.
The optional third argument sets the character position at which the search begins. Writing =FIND(“-”, A2, 5) tells Excel to ignore the first four characters and start looking for a hyphen from position 5 onwards. This is useful when a string contains the same character more than once, such as a code with several hyphens, and you want the second or third occurrence rather than the first.
Which function should you choose? Use SEARCH when you want a forgiving match that ignores capitalisation, which is the usual case for checking descriptions, names, and categories. Use FIND when capitalisation matters, for example when product codes use case to distinguish variants, or when you are validating data entry and a lowercase version should not count as a match.
| Function | Case-sensitive? | Wildcards? | Best for |
|---|---|---|---|
| FIND | Yes | No | Exact codes where capitalisation matters |
| SEARCH | No | Yes | Everyday checks of words in descriptions |
| ISNUMBER with FIND or SEARCH | Depends on the function used | Depends on the function used | Turning a position into TRUE or FALSE |
| COUNTIF with wildcards | No | Yes | Counting or flagging whole cells that contain a word |
Turning the Result Into a Simple Yes or No
A position number is useful for extraction, but most people want a simpler answer: does the cell contain this text or not? The standard method is to combine SEARCH or FIND with ISNUMBER and IF.
ISNUMBER checks whether a value is a number. Since a successful FIND or SEARCH returns a number, and a failed one returns an error, ISNUMBER gives TRUE for a match and FALSE for no match. The formula =ISNUMBER(SEARCH(“refurbished”, A2)) therefore returns TRUE when cell A2 mentions refurbished, in any capitalisation, and FALSE when it does not.
Wrapping that in IF turns TRUE and FALSE into words. The formula =IF(ISNUMBER(SEARCH(“refurbished”, A2)), “Yes”, “No”) returns the word Yes for a match and No otherwise. You can replace Yes and No with any labels you prefer, such as “Contains code” and “Missing code”, or “Flagged” and a blank. Using a blank for the no-match case, written as two double quotes with nothing between them, keeps a checking column tidy.
There is also a shorter method using IFERROR. The formula =IFERROR(SEARCH(“refurbished”, A2), “Not found”) returns the position number when there is a match and the words “Not found” when there is not. IFERROR is convenient, but ISNUMBER with IF is usually clearer when the goal is a yes-or-no flag, because the output is a label rather than a mixture of numbers and text.
One warning applies to both methods: SEARCH treats certain characters as wildcards. A question mark stands for any single character, and an asterisk stands for any sequence of characters. If you search for text that genuinely contains a question mark or asterisk, SEARCH may match more than you intended. To search for a literal question mark, place a tilde before it in the search text. The same applies to a literal asterisk and to a literal tilde itself. This detail rarely matters, but it explains occasional surprising matches in codes and file names.
Checking Many Cells and Counting Matches
Once a single-cell formula works, applying it to a whole column is straightforward. Enter the formula in the first row of a helper column, then fill it down by double-clicking the fill handle or copying it down the column. If your search term sits in its own cell, reference that cell instead of typing the word into every formula. For example, if the search term is in cell B1, write =IF(ISNUMBER(SEARCH($B$1, A2)), “Yes”, “No”). The dollar signs lock the reference to B1, so the formula keeps pointing at the search term as it fills down, while the A2 reference moves row by row. Changing the term in B1 then updates every result at once.
To count how many cells contain a word, COUNTIF with wildcards is the quickest route. The formula =COUNTIF(A2:A100, “*refurbished*”) counts every cell in the range A2 to A100 that contains the word refurbished anywhere in its text. The asterisks on either side mean “any characters before and after”, which is what turns an exact-match count into a contains count. Without the asterisks, COUNTIF would only count cells that equal the word exactly.
COUNTIF is not case-sensitive, so it matches “Refurbished”, “REFURBISHED”, and “refurbished” alike. That is usually what you want for counting descriptions. If you need a case-sensitive count, you would need a longer formula built from SUMPRODUCT and FIND, and most everyday checks do not require that extra complexity.
A related task is filtering a list to show only matching rows. In current versions of Excel, the FILTER function can do this directly, returning only the rows where a SEARCH succeeds. In older versions, the practical route is to add a helper column with the yes-or-no formula described above, then filter on that column. Both approaches give the same shortlist. The helper-column route has the advantage of working in almost every version of Excel, which matters when a file will be shared with colleagues using different versions.
Extracting Text Around a Match
Finding a position is only the first step when you need to pull part of a code out of a longer string. Suppose a cell contains “SKU-BR-88410-UK” and you want the middle section. You can use FIND to locate the hyphens, then MID to extract the characters between them. This is more involved than a simple contains check, but it follows the same logic: find the position, then cut the string at that point.
For a simpler case, LEFT and RIGHT extract characters from the ends of a string. If a code always begins with a three-letter prefix, LEFT(A2, 3) returns that prefix. If a reference always ends with two letters, RIGHT(A2, 2) returns those letters. Combining these with FIND lets you handle codes of varying length, because FIND tells you where the dividing character sits instead of assuming a fixed length.
A worked example helps. If A2 contains “Order-12345” and you want just the digits, the formula =MID(A2, FIND(“-”, A2) + 1, 99) returns “12345”. FIND locates the hyphen, adding 1 moves past it, and MID takes the characters from that point onwards. The number 99 is simply a large number chosen to capture everything that remains. This pattern appears constantly in data cleaning, and it is worth learning even if you only need contains checks today.
When the text you are extracting may not be present at all, wrap the formula in IFERROR so that a missing match gives a blank or a message instead of an error. For example, =IFERROR(MID(A2, FIND(“-”, A2) + 1, 99), “”) returns the digits when a hyphen exists and a blank when it does not. That small addition makes a formula safe to fill down a column of mixed data.
Common Errors and How to Fix Them
The most common error is #VALUE!, which almost always means “no match found” or “the search text is empty in a way Excel cannot handle”. Before assuming a formula is wrong, check the cell contents for extra spaces, which are a frequent cause of missed matches. A word with a leading or trailing space looks identical on screen but will not match a search term without that space. The TRIM function removes extra spaces, and combining it with the search, as in SEARCH(“refurbished”, TRIM(A2)), often fixes the problem.
Another common issue is searching for numbers stored as text, or text stored as numbers. Excel treats the number 123 and the text “123” as different kinds of value in some functions, although FIND and SEARCH generally work on the displayed text. If a search for digits fails unexpectedly, check whether the cell is formatted as text or as a number, and consider converting the range to a consistent format before running the check.
Case sensitivity causes a third class of surprise. A FIND formula that returns an error while a SEARCH formula succeeds is not broken. It is telling you that the capitalisation differs. Decide which behaviour you actually want, then choose the function to match. For most descriptive text, SEARCH is the safer default.
Finally, remember that FIND and SEARCH work on one cell at a time. They do not search a whole sheet at once. If you need to find a value anywhere in a workbook, use the manual Find tool with Ctrl plus F, or build a helper column on each sheet you need to check. Mixing up the two jobs is a common source of frustration, but the distinction is simple: manual Find locates a value for a human, while FIND and SEARCH test values for formulas.
For a related spreadsheet task, see this guide to counting cells with text in Google Sheets, and for handling text in documents, this guide to converting a table to text in Word.
Frequently Asked Questions
Which Excel function finds text within a string?
FIND and SEARCH both do this. FIND is case-sensitive and does not support wildcards. SEARCH ignores case and supports wildcards. Both return the starting position of the match, or an error when there is no match.
How do I get a Yes or No instead of a position number?
Use =IF(ISNUMBER(SEARCH(“word”, A2)), “Yes”, “No”), replacing “word” with your search term and A2 with the cell to check. ISNUMBER turns a successful position into TRUE and a failed search into FALSE, and IF turns those into labels.
Why does my formula show #VALUE!?
In most cases, #VALUE! means the text was not found. It can also mean the search range contains an unexpected value type. Wrap the formula in IFERROR or ISNUMBER to turn that error into a clean message or a No.
Can I find text in a whole column at once?
Yes. Put the formula in a helper column and fill it down, or use COUNTIF with wildcards, such as =COUNTIF(A2:A100, “*word*”), to count matching cells in a range.
Does SEARCH treat question marks and asterisks specially?
Yes. In SEARCH, a question mark stands for any single character and an asterisk stands for any sequence of characters. Put a tilde before either character to search for it literally.
Conclusion
To find text within string Excel data, start with SEARCH for a forgiving, case-insensitive check, or FIND when capitalisation matters. Use ISNUMBER and IF to turn the position into a clear Yes or No, use COUNTIF with wildcards to count matches across a range, and use MID with FIND to extract text around a match. Keep the search term in its own cell so it is easy to change, watch out for extra spaces and case differences, and wrap formulas in IFERROR where a missing match should show a blank rather than an error. With those five habits, locating text inside spreadsheet cells becomes a reliable, repeatable task rather than a slow manual scan.
