Text function
SEARCH
Where is it, ignoring capitals?
Purpose
Locate a marker without worrying about capitals
Returns
A number: the position of the first character of the text you looked for
Syntax
=SEARCH(find_text, within_text, [start_at])
| Argument | Required | What goes here |
|---|---|---|
find_text | Required | The text you are looking for. |
within_text | Required | The cell to look inside. |
start_at | Optional | Where to start looking. |
How to use SEARCH
SEARCH does what FIND does and ignores the difference between capitals and lower case.
That is exactly what an import needs. A supplier column that arrives as "calder UTILITIES" in one row and "Calder Utilities" in the next will defeat FIND and not SEARCH.
Use FIND when the case is meaningful, which on a ledger is mostly account codes and reference prefixes. Use SEARCH on anything a person typed.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reference | Supplier name | Amount | Posted |
| 2 | GL-6100-8801 | brightwell paper | 1500 | Y |
| 3 | GL-6200-8802 | calder UTILITIES | 320 | y |
| 4 | GL-6100-8803 | meridian freight ltd | 2750 | |
| 5 | GL-6300-8804 | ashgrove catering | 184 | Y |
| 6 | GL-6200-8805 | THORNBURY print | 760 | |
| 7 | GL-6100-8806 | Brightwell Paper | 1120 | Y |
| Formula | Result |
|---|---|
=SEARCH("brightwell",B2)Found, even though the cell holds it in lower case and the search term is spelled the same way. | 3 |
=SEARCH("UTILITIES",B3)Capitals in the search term, capitals in the cell. Case makes no difference either way. | 8 |
=ISNUMBER(SEARCH("freight",B4))The pattern worth knowing: SEARCH wrapped in ISNUMBER answers whether a value contains a word, rather than where in it. | TRUE |
Notes
- SEARCH accepts ? for one character and * for any run of characters. FIND takes neither.
- SEARCH errors when the text is not found. In Excel that error is a value ISNUMBER reads as FALSE; on this site the error stops the formula instead, so put IFERROR around it when you want the no rather than the halt.