Excel track · Exercise 52 of 86 Paid
FIND and SEARCH: case-sensitive or not
Choose FIND for a case-sensitive position, SEARCH when case should not matter.
Intermediate
Hyphenated codes and typed labels holding lower-case letters
Read-only data · screen 1 of 2 · answer in B2
In B2, report the position of the hyphen in the code in A2.
| A | B | |
|---|---|---|
| 1 | Code | Position |
| 2 | AN-1042 | |
| 3 | BO-2299 | |
| 4 | CY-3140 |
In B2, report the position of the first N in the label in A2 whatever its case.
| A | B | |
|---|---|---|
| 1 | Label | Position |
| 2 | banana grove | |
| 3 | mango stall | |
| 4 | lemon crate |
Reference answer
What it does
FIND and SEARCH answer the same question: where inside this text does the needle start. Both count from 1 and both return a position, never the text they found. The only difference is case, and it decides which one you write.
FIND matches case. A code is case-sensitive text, so the hyphen in AN-1042 is found at position 3, and a capital N would not be found in a lower-case label. SEARCH ignores case and finds north, North and NORTH alike.
Use FIND when the character is part of a code and SEARCH when the text was typed by someone else. The wrong choice does not always raise an error: FIND on a label returns #VALUE! only once the cases disagree. The second screen asks for a position in a label, so the function name is what the grader checks.
Syntax
=FIND(needle,text) matches case; =SEARCH(needle,text) ignores it; both answer a positionWorked example
On Hyphenated codes and typed labels holding lower-case letters, =FIND("-",A2) returns 3.
=FIND("-",A2)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The text you are looking for is not in the cell, and FIND raises #VALUE! instead of returning 0. |
Wrong function | SEARCH returns 3 for the hyphen as well, but this screen is graded on FIND, so the answer does not pass. |
At work
Locating a separator or a word inside a code or a label before slicing it.