Lesson 21 of 45, Text cleanup
TEXTBEFORE, TEXTAFTER & FIND
Quick answer
Split text at a delimiter without counting characters.
- Key formula
=TEXTBEFORE(A2,"@")(Everything before @)
Watch it done
3 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
TEXTBEFORE(text, delimiter) and TEXTAFTER(text, delimiter) split text at a character like @ or a dash, wherever it falls.
In older Excel, combine FIND, which returns the position of a character, with LEFT or MID. FIND is case-sensitive; SEARCH is not.
Syntax
=TEXTBEFORE(A2,"@")Everything before @=TEXTAFTER(A2,"@")Everything after @=LEFT(A2,FIND("@",A2)-1)Classic versionWatch out for
- FIND returns the position of the @ itself, so subtract 1 to stop before it.
- TEXTBEFORE and TEXTAFTER need Microsoft 365.
The exercises
- Username. In B2, return everything before the @. Fill down to B6.
- Domain. In C2, return everything after the @. Fill down to C6.
- The classic way. In D2, extract the username again using LEFT and FIND, the method that works in every Excel version. Fill down to D6.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Username
- Domain
- The classic way