For a case-insensitive contains test, enter =ISNUMBER(SEARCH("west",A2)) in B2. To return labels, enter =IF(ISNUMBER(SEARCH("west",A2)),"West","Other") in C2. Fill down and compare the results with the source text. SEARCH matches part of a cell, so western depot also matches west.
On this page9
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
logical_test | required | A condition that evaluates to TRUE or FALSE, such as A2>100. |
value_if_true | required | What the formula returns when the condition is TRUE. |
value_if_false | optional | What the formula returns when the condition is FALSE; optional but strongly recommended. |
Related functions
Use an empty sheet. Paste the sample into A1, including its header; A5 must be genuinely empty. Leave columns B and C available for the results.
Enter =ISNUMBER(SEARCH("west",A2)) in B2 to show TRUE or FALSE for the text in A2.
Enter =IF(ISNUMBER(SEARCH("west",A2)),"West","Other") in C2 to return West for a match and Other otherwise.
Select B2:C2 and fill down through row 6. Check that row 3 refers to A3 and row 6 refers to A6; keep column A unchanged.
Compare all five rows with the expected table. The blank A5 returns FALSE in B5 and Other in C5. WEST depot and western depot both return West.
Check five contains-text results
Paste the source column into A1 of an empty sheet. A5 is genuinely empty. Enter the TRUE/FALSE formula in B2 and the IF label formula in C2, then fill both through row 6. Keep the original text in A and compare all five rows.
A1TRUE/FALSE formula for B2
=ISNUMBER(SEARCH("west",A2))IF label formula for C2
=IF(ISNUMBER(SEARCH("west",A2)),"West","Other")Case-sensitive comparison for a separate helper cell
=ISNUMBER(FIND("west",A2))| A: Original text | B: Contains west? | C: IF label |
|---|---|---|
| North-West Region | TRUE | West |
| WEST depot | TRUE | West |
| South | FALSE | Other |
| FALSE | Other | |
| western depot | TRUE | West |
If columns are cut off, scroll the table sideways.
- B2:B6 is TRUE, TRUE, FALSE, FALSE, TRUE. C2:C6 is West, West, Other, Other, West.
- A5 is empty; it does not contain the word blank or a space. The two output formulas still occupy B5 and C5.
- North-West Region and WEST depot match despite case differences. western depot matches because west is a substring.
- After filling down, B6 and C6 refer to A6. No helper formula should overwrite the source column.
- The copy buttons provide sample text and formulas for your worksheet. This walkthrough does not calculate inside your workbook.
Documentation reviewed .
Microsoft: Case-insensitive contains-text checksMicrosoft: SEARCHMicrosoft: IS functionsMicrosoft: IFMicrosoft: Case-sensitive contains-text checksMicrosoft: FIND and SEARCH #VALUE! troubleshootingMicrosoft: Fill a formula down on WindowsMicrosoft: Fill formulas on Mac
Excel for Windows: Enter the formulas in B2 and C2, then select B2:C6 and use Home > Fill > Down. Check the references and five results.Excel for Mac: Enter the formulas in B2 and C2, then select B2:C2 and drag the fill handle down through row 6. Check the references and five results.
How SEARCH, ISNUMBER and IF fit together
SEARCH returns the position where the search text begins. For North-West Region, searching for west returns 7 even though the source has an uppercase W. ISNUMBER turns that numeric result into TRUE. When the text is absent, SEARCH returns #VALUE! and ISNUMBER returns FALSE. IF uses that TRUE or FALSE as its logical_test, with West as value_if_true and Other as value_if_false. The IF syntax and argument table above still apply; the important task-specific part is the SEARCH test inside the first argument.
Check case, substrings and the blank row
The five-row example returns West for North-West Region, WEST depot and western depot. South and the genuinely empty A5 return Other. The search term is the fixed, non-empty text west. This is a substring check, not a whole-word check or an equality test. =A2="west" checks the whole cell instead. For a case-sensitive substring check, use =ISNUMBER(FIND("west",A2)). That returns FALSE for North-West Region and WEST depot, but TRUE for western depot. FIND therefore changes the matching rule, not just the formula spelling.
Troubleshooting and task boundaries
Keep the search term and label text in straight double quotes, and check each filled formula reads the source cell on the same row. A bare SEARCH can show #VALUE! when no match exists; use the ISNUMBER wrapper when you want a TRUE/FALSE test. Other means the test did not find a numeric position, so it is not proof that an input is error-free: check source errors separately. SEARCH treats ? and * as wildcards; prefix a literal question mark or asterisk with ~ when that is the character you need to find. The example uses plain west and no wildcard. If you later use a criteria cell, keep its search term non-empty or define a separate blank-criteria rule. Returning a label, counting matching cells and applying conditional formatting are different tasks. This page teaches the one-cell label formula, not a count or a formatting rule.
Common mistakes
- Using a whole-cell equality check when the term can appear inside a longer phrase.
- Treating SEARCH as case-sensitive, or treating a substring match as a whole-word match.
- Typing the word blank into
A5instead of leaving the source cell genuinely empty. - Entering the helper formula in column A and overwriting the text it needs to inspect.
- Assuming Other validates the input: inspect source errors separately.
Frequently asked questions
How do I return text when a cell contains a word?
Use =IF(ISNUMBER(SEARCH("west",A2)),"West","Other") for this case-insensitive substring example. Change the quoted term and the two labels deliberately, then check matching and non-matching inputs.
Does SEARCH distinguish uppercase and lowercase?
No. Searching for west matches WEST depot. For a case-sensitive substring test, use =ISNUMBER(FIND("west",A2)); this does not match WEST depot.
What happens when the source cell is blank?
With the non-empty search term west and a genuinely empty A5, =ISNUMBER(SEARCH("west",A5)) returns FALSE and the IF label formula returns Other. A blank search term is a separate input condition; do not use this blank-source result as a blank-criteria rule.
Why does western depot match west?
SEARCH looks for text inside the cell, including inside a longer word. The label formula is a substring check. A whole-word test needs an explicit definition of word boundaries.
Does this formula count cells or change their formatting?
No. It returns one label per source cell in a helper column. Counting matching cells and applying conditional formatting need their own range or rule setup.