To check whether cell A2 contains the word “lambda,” use =ISNUMBER(SEARCH("lambda",A2)); the result is TRUE if a match is found and FALSE if it is not. If you want to reuse that check with different cells and search terms, define a custom LAMBDA function in Excel for Microsoft 365.
What “contains” means in Excel
A contains test looks for a piece of text anywhere inside a cell’s value. For example, if A2 contains “The lambda function is reusable,” a search for “lambda” matches even though the cell has other words around it. This differs from an exact-match test, which would require the entire cell value to equal the search term.
In Excel, the usual building blocks are SEARCH and ISNUMBER. SEARCH returns the position where it finds the requested text. ISNUMBER turns that position into TRUE; if SEARCH cannot find the text, it returns an error, which ISNUMBER converts to FALSE. Put together, they create a practical yes-or-no test.
The direct formula in the opening example is enough for a one-off check. LAMBDA is useful when you repeat the same check across many formulas or workbooks: it lets you give the logic a name and call it with different inputs. LAMBDA does not change what “contains” means; it packages the test so you can reuse it.
Use a direct contains formula
With the text to inspect in A2 and the word to find being “lambda,” use:
=ISNUMBER(SEARCH("lambda",A2))
SEARCH is not case-sensitive, so the formula also matches “Lambda” or “LAMBDA.” The result is a Boolean value, TRUE or FALSE, which can be used in other formulas, conditional formatting rules, or filters. To show a more readable result instead, wrap the test in IF.
With the text to inspect in A2 and the word to find being “lambda,” use:
=IF(ISNUMBER(SEARCH("lambda",A2)),"Found","Not found")
That formula displays Found when the cell contains the term and Not found otherwise. To search a variable term rather than hard-code one, put the search text in B2 and refer to it:
With the text to inspect in A2 and the search term in B2, use:
=ISNUMBER(SEARCH(B2,A2))
This layout is handy for a list. Enter the formula beside the first row, then fill it down to check each cell against the corresponding search term. In Excel for Windows, the fill handle at the bottom-right corner of the selected cell can be dragged down; double-clicking it can fill alongside a neighboring column of data. Excel for Mac uses the same formula and fill-handle approach.
Turn the test into a reusable LAMBDA
A LAMBDA function lets you define a formula with named inputs. For this example, name the inputs text and part: the function checks whether part appears within text. The formula uses SEARCH for the match and ISNUMBER to return TRUE or FALSE.
For the function definition, assume the workbook’s text data is in A2:A100; the definition itself accepts its text and search term as arguments. In Name Manager, enter this in the Refers to box:
=LAMBDA(text,part,ISNUMBER(SEARCH(part,text)))
Once saved with a name such as CONTAINS_TEXT, call it in a worksheet like this:
With the text to inspect in A2 and the search term “lambda,” use:
=CONTAINS_TEXT(A2,"lambda")
The result is the same TRUE or FALSE you would get from the direct formula. Change A2 or the search term to reuse the function without rewriting its logic. The chosen name is a name you create in your workbook, not a built-in Excel function; avoid spaces in the name and choose one that clearly describes its purpose.
Create and save the named function
In Excel for Microsoft 365 on Windows, use these steps:
- Open the Formulas tab and select Name Manager.
- Select New.
- Enter
CONTAINS_TEXTin the Name box. - Enter the LAMBDA definition in the Refers to box, including the leading equals sign.
- Select OK, then close Name Manager.
Now enter a call such as =CONTAINS_TEXT(A2,"lambda") in a worksheet cell. If Excel reports a problem while saving the definition, check that the opening and closing parentheses are balanced, the argument separators are commas, and the formula starts with an equals sign. The name and arguments must also be spelled consistently when you call the function.
LAMBDA is available in Microsoft 365 and Excel 2024. The formula syntax is the same in current Excel for Mac, although ribbon placement and dialog details can differ by platform and update. Named functions are stored with the workbook, so another workbook will not automatically know this custom name. If you need the function in another file, define it there too or copy the relevant defined name.
Case sensitivity and exact matches
SEARCH ignores letter case. That makes it suitable for most ordinary text checks, such as finding a product name regardless of capitalization. If capitalization matters, use FIND instead; FIND is case-sensitive but otherwise serves a similar purpose.
With the text in A2 and the case-sensitive search term “Lambda,” use:
=ISNUMBER(FIND("Lambda",A2))
This returns TRUE for “Lambda” but not for “lambda.” If the goal is to check whether the entire cell equals a value, a contains formula is not the right test. With the value in A2 and the required exact text “lambda,” use:
=A2="lambda"
That comparison is not case-sensitive in standard Excel formulas. Choose the test based on the question: SEARCH or FIND checks for a substring; an equality comparison checks the whole cell’s value. A common mistake is to use a contains test when an exact match is required, which can produce a match for a longer phrase that happens to include the term.
Common problems and edge cases
If the cell does not contain the search term, SEARCH returns an error. That is expected behavior; ISNUMBER converts the result into FALSE. If the cell itself contains an error value, however, the error may pass through the test. If you want errors in the source cell to count as “not found,” wrap the test in IFERROR.
With the text to inspect in A2 and the search term “lambda,” use:
=IFERROR(ISNUMBER(SEARCH("lambda",A2)),FALSE)
This returns FALSE if the search fails or if A2 contains an error. Consider whether that is appropriate for your spreadsheet: treating bad source data as a simple non-match can hide a data-quality issue. In a review or audit sheet, it may be better to leave the error visible so it can be investigated.
SEARCH treats the characters * and ? as wildcards. An asterisk can match any number of characters, and a question mark can match one character. So a search term containing one of those characters may match more than its literal spelling suggests. To search for a literal wildcard character, precede it with a tilde in the search text: ~* searches for an asterisk, and ~? searches for a question mark.
A blank search term is another special case: searching for an empty string can be treated as a match. If B2 may be blank, decide whether that should count as TRUE. To return FALSE for blank search terms, use this pattern:
With the text to inspect in A2 and the optional search term in B2, use:
=IF(B2="",FALSE,ISNUMBER(SEARCH(B2,A2)))
Finally, check for extra spaces or different characters when a visible match is not detected. A copied value may contain an unexpected space, line break, or a look-alike character. The Greek letter λ and the written word “lambda” are different text, so search for the exact form present in the cell. For messy imported data, inspect the source value before adding more complicated formula logic.
When to use LAMBDA—and alternatives
Use the direct SEARCH formula if you only need a few checks or want the logic to be immediately visible to anyone reading the sheet. Use a named LAMBDA when the same test appears repeatedly, when you want a consistent definition across a workbook, or when changing the logic later should require editing only one definition. A custom function can make formulas shorter, but readers of the workbook need to know that the name is defined in Name Manager.
If the task is simply to find matching rows rather than label each row TRUE or FALSE, a filter or Excel’s Find command may be more convenient. A formula is preferable when the result needs to update automatically, feed another calculation, or remain visible as part of the worksheet. For a one-time manual search, press Ctrl+F on Windows and enter the text. On Mac, use Command+F. These search commands locate text; they do not create a reusable TRUE/FALSE result.
If you want to count matches, first create a TRUE/FALSE result for each row or use a suitable counting formula based on your data layout. Keep in mind that wildcard behavior, capitalization, blank search terms, and errors can affect results. Test the formula on a few known examples—including one expected match and one expected non-match—before relying on it across a large range.
Frequently Asked Questions
Does Excel have a built-in CONTAINS function?
No. A common way to test for text within a cell is to combine SEARCH and ISNUMBER. You can also define a custom LAMBDA and give it a name such as CONTAINS_TEXT.
Is SEARCH case-sensitive?
No. SEARCH ignores capitalization. Use FIND instead when upper- and lowercase letters should be treated differently.
Can I use LAMBDA directly in a cell?
Yes. Excel can evaluate a LAMBDA when it is called with arguments, but for repeated use it is usually clearer to save the definition as a named function in Name Manager.
Why does my formula return TRUE for a partial match?
That is what a contains test is designed to do: it looks for the term anywhere within the cell. Use an equality comparison if you need the whole cell to match exactly.
Does this work for the Greek letter λ?
Yes. SEARCH can look for that character if the cell contains the same character. Enter or copy λ as the search text; the word “lambda” will not match the Greek letter automatically.
Related
- Can AI Replace Excel Jobs? The Work It Can Do
- Calendar Timeline Template Excel: Build Your Own
- Calendar Planner Template in Excel: Build and Use One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIFS Formula for Date Range in Excel With ExamplesTo count dates within a range in Excel, use COUNTIFS with a start-date criterion and an end-date criterion.…
- Excel Column Total Formula: SUM, AutoSum, and TipsTo total a column in Excel, use =SUM(A2:A100) for a specific range, or =SUM(A:A) for the entire column.…
- Excel AND in MATCH: Find Rows With Multiple CriteriaTo use AND conditions with Excel MATCH, compare each criteria range with its requested value and multiply the…
- Microsoft Excel Date Today: TODAY Formula and Useful TipsTo show today’s date in Microsoft Excel for Microsoft 365 on Windows, enter =TODAY() in a cell and…
- Insert a Checkbox in Excel Using a Formula: Step-by-StepTo show a formula-driven checkbox in Excel, put a TRUE or FALSE value in A2 and, with that…
- Filter an Excel Table by Value: Steps, Tips, and FixesTo filter an Excel table by value in Microsoft Excel for Microsoft 365 on Windows, click the arrow…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.