The FIND function in Excel
FIND returns the position of one piece of text inside another, counting from 1. On its own a position isn't very exciting, but it's the key to splitting text whose parts vary in length: find the @, the space or the hyphen, then hand that position to LEFT, MID or RIGHT. FIND is case-sensitive, which is the main way it differs from SEARCH.
Syntax
=FIND(find_text, within_text, [start_num])
| find_text | The text to look for, in quotes, like "@" or " ". |
|---|---|
| within_text | The cell or text to search in. |
| start_num | Optional. The position to start looking from. Defaults to 1, the first character. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In C2, use FIND with LEFT to extract the username (everything before the @) from the email address in B2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Username | ||||||
| 2 | imoreau@northwind.com | ||||||
| 3 | darius.cole@bluepeak.io | ||||||
| 4 | mtanaka88@contoso.org | ||||||
| 5 | p.ortiz@fabrikam.net | ||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: FIND tells you where the @ is. LEFT should take one character fewer than that.
Examples
=FIND("@",B2)→ 9The @ is the 9th character of Ana's email, so FIND returns 9.
=FIND("-",C2,FIND("-",C2)+1)→ 8The inner FIND locates the first hyphen. Starting one character after it, the outer FIND returns the position of the second hyphen.
=IFERROR(FIND("red",C3),"Not found")→ "Not found"The code contains "Red" with a capital R. FIND is case-sensitive, so it doesn't match "red" and IFERROR shows the message.
Example data
| 1 | Full name | Product code | |
|---|---|---|---|
| 2 | Ana Ruiz | ana.ruiz@contoso.com | WX-2201-blue |
| 3 | Ben Okoro | ben.okoro@fabrikam.org | WX-2202-Red |
Common mistakes
#VALUE! when the text isn't there
FIND returns #VALUE! if it can't find the text, for example a name with no space. Wrap it in IFERROR, or test first with
ISNUMBER(FIND(...)).Capitals don't match
FIND("red",A2)won't find "Red" or "RED". If case doesn't matter, use SEARCH instead.Expecting wildcards to work
FIND treats
*and?as ordinary characters, soFIND("WX*",A2)looks for a real asterisk. Use SEARCH for wildcard patterns.Including the separator in the result
FIND gives the position of the separator itself. Use
FIND(" ",A2)-1for the length before it and+1to start after it.
FAQ
What's the difference between FIND and SEARCH?+
Both return a position. FIND is case-sensitive and doesn't support wildcards. SEARCH ignores case and understands * and ?.
How do I find the second occurrence of a character?+
Start the search just after the first one: =FIND("-",A2,FIND("-",A2)+1).
How do I check if a cell contains text with FIND?+
Wrap it in ISNUMBER: =ISNUMBER(FIND("VIP",A2)) returns TRUE only if A2 contains "VIP" in exactly those capitals.
Lesson
Master FIND step by step: FIND and SEARCH
Locate a character or word inside text with FIND or SEARCH, then use that position to split names and pull out order numbers.