Spreadsheet Practice

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 @)
Pro8 minutes, 3 exercisesTEXTBEFORETEXTAFTERFIND

Watch it done

3 steps

Watch 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 version

Watch out for

  • FIND returns the position of the @ itself, so subtract 1 to stop before it.
  • TEXTBEFORE and TEXTAFTER need Microsoft 365.

The exercises

  1. Username. In B2, return everything before the @. Fill down to B6.
  2. Domain. In C2, return everything after the @. Fill down to C6.
  3. 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.

  1. Username
  2. Domain
  3. The classic way