FIND
Finds the position where one piece of text starts inside another. Usually needed to then cut out part of the text with LEFT or MID.
FIND(find_text, within_text, [start_num])- [ ]optional part
- find_text
- What to look for: a character or word in quotes, for example " ".
- within_text
- Where to look, for example cell A2.
- start_num
- Which character to start searching from. If left out, the search starts from the first one.Optional
Return value
A number: the position where the text starts, counting from 1. Case matters. If the text isn't found, you get #VALUE!.
Examples
First name before the space
fx
A
B
C
D
1
2
3
4
5
6
7
Employee
Space position
First name
John Smith
Emily Davis
Mike Brown
Sarah Johnson
Sheet1
Details
FIND returns the number of the character where the text you're looking for starts. For example, in "John Smith" the space is the 5th character.
The function is case-sensitive: "m" and "M" are different for it. If case doesn't matter, use SEARCH.
If the text isn't found, FIND returns #VALUE!. You can catch it with IFERROR.
Common mistakes
- The arguments are swapped: first what to look for, then where.
- Case was ignored: FIND("m",A2) won't find the capital "M" in "Miami".
- The separator position was passed to LEFT as is, and the space ended up in the result. To take the text before it, subtract 1.
- The row doesn't have the character: #VALUE! appears and spreads to every formula that refers to it.