Lesson 16 of 29, Text cleanup
TEXTBEFORE, TEXTAFTER & FIND
Split text at a delimiter without counting characters.
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.
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 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Username
- Domain
- The classic way