FIND: Locating a Character

About this lesson

FIND returns the position of one piece of text inside another, counting from 1. It treats capitals and lower case as different characters.

By the end you can

find the position of a character in a cell with FIND.
say what FIND does when the character is missing, and trap it.

Practises FIND SEARCH MID ISNUMBER

The idea

Two arguments to begin with: what to look for, then where to look. That is the opposite order from the way people say it. It is the only thing to remember. A third argument says where to start looking. That is how you get at the second occurrence of something instead of the first.

What it returns when the text is not there is the part that matters. Not zero, not blank: #VALUE!. That is the right decision. A position of zero would be impossible to tell apart from a real answer once it went into arithmetic. But it means anything built on FIND breaks on the one row in a thousand that does not fit the pattern. ISNUMBER around it turns the error into a plain FALSE. The Errors module adds IFERROR for the cases where a column needs a value, not a verdict.

The mistake to watch for

Two things. FIND's arguments are the opposite way round from how people say them: what to find first, then where. And it returns #VALUE!, not 0, when the text is not there. So a column built on FIND breaks on the one row that does not fit. Wrap it in ISNUMBER when the question is whether the text is present at all.

Where this comes up again

ISNUMBER, ISTEXT, ISBLANK and ISERROR Errors and Robust Formulas
Errors: Putting It Together Errors and Robust Formulas
SUBSTITUTE: Replacing Text Text: Building and Cleaning
TEXT and VALUE: Converting Between the Two Text: Building and Cleaning

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Find the position of the space in the first name in A2, and put it in B2.

To begin, type it exactly:

=FIND(" ",A2)

Row	A	B	C
1	Full name	Space at	Result
2	Priya Raman
3	Jonas Vogel
4	Aiko Tanaka
5
6	Sample	Order-A-2024
7
8

Every step

1

Find the position of the space in the first name in A2, and put it in B2. To begin, type it exactly: =FIND(" ",A2).

Hint. The thing to find goes first, in quotes.
2

FIND cares about capitals. Read =FIND("r",A2) and say what it returns for Priya Raman.

Hint. There is more than one r. Only one of them matches exactly.
3

FIND can start part of the way along. In B4, find the position of the second hyphen in B6. Start the search after the first one.

Hint. Three arguments: what, where, and from which position.
4

When the text is not there, FIND does not return 0 or a blank. It returns the error #VALUE!. The standard way to turn that into a plain yes or no is ISNUMBER. It answers TRUE when what it is given is a number, and FALSE otherwise. There is no z in Priya Raman. In C2, type =ISNUMBER(FIND("z",A2)) and see which it answers.

Hint. One function around the other.
5

The trap, and it is the case rule again. Read =ISNUMBER(FIND("raman",A2)) and say what it returns for Priya Raman.

Hint. Compare the first letter of the word with the cell.