Lessons Lesson 57

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

By Ali, Founder, Excel Lessons

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.

Read more about this

Where this comes up again

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 gives the position of one piece of text inside another.

In B2, type:

=FIND(" ",A2)

Remember: text arguments go in quotes, even a single space.

B2
Row A B C D E F G
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. 1

    FIND gives the position of one piece of text inside another. In B2, type: =FIND(" ",A2). Remember: text arguments go in quotes, even a single space.

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

  2. 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. 3

    FIND can start searching part-way through the text. In B4, find the second hyphen in B6, starting after the first. Shape: =FIND(text,within,start).

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

  4. 4

    FIND returns an error when the text is missing. ISNUMBER turns that into TRUE or FALSE. In C2, test whether A2 contains "z" with ISNUMBER and FIND. Shape: =ISNUMBER(FIND(text,within)).

    Hint. One function around the other.

  5. 5

    FIND treats capitals and lower case as different characters. Read =ISNUMBER(FIND("raman",A2)) and say what it returns.

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