Excel FIND Function: locate text (case-sensitive)

FIND returns the position where some text starts inside a cell. In “asha@mail.in”, the @ is character 5.

The formula

=FIND(find_text, within_text, [start_num])

Not found: #VALUE!.

FIND vs SEARCH

FIND is case-sensitive and has no wildcards: =FIND("A","banana") is an error because there is no capital A. SEARCH ignores case. Pick FIND when case matters (product codes like “aB12”).

Text before a character

Username from an email:

=LEFT(A2,FIND("@",A2)-1)

“asha@mail.in” → asha.

Text after a character

Domain from an email:

=MID(A2,FIND("@",A2)+1,100)

→ mail.in. The 100 simply means “the rest”. See MID.

The 2nd occurrence

Start searching one character after the first match:

=FIND("-",A2,FIND("-",A2)+1)

In “IN-MH-2045” the second dash is at 6.

Does it contain the text?

=ISNUMBER(FIND("VIP",A2)) — TRUE only for capital “VIP”.

Excel 365 shortcut

=TEXTBEFORE(A2,"@") and =TEXTAFTER(A2,"@") replace most FIND + LEFT/MID combinations.