=FIND(find_text, within_text, [start_num]) returns the position of find_text: FIND("@","ana@rowan.example") is 4. SEARCH does the same with two differences:
- FIND is case-sensitive; SEARCH is not.
- SEARCH accepts wildcards (
? and *); FIND doesn't.
Both return #VALUE! when the text isn't there, so ISNUMBER(SEARCH("refund",B2)) works as a "contains" test.
The position is what you feed to LEFT, MID and RIGHT: =LEFT(A2,FIND("@",A2)-1) is everything before the @. The -1 drops the @ itself, and forgetting it's the most frequent slip.