Splitting text with TEXTBEFORE and TEXTAFTER

1 / 4

Each email is a username, an @, then a domain. Pull the domain out into column B.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABC
1EmailDomain
2kofi.patel@greenleaf.example
3jamal.novak@oakfield.example
4isla@northgate.example
5daniel@northgate.example
6yusuf.dubois@oakfield.example
7tariq.li@northgate.example
8nadia.patel@brightway.example
9quinn.evans@bluepeak.example
10
11
12
13
emails
domain
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How TEXTBEFORE and TEXTAFTER works

  • What do TEXTBEFORE and TEXTAFTER do?

    They split text at a character you choose, called the delimiter:

    • =TEXTBEFORE(A2,"@") gives everything before the @.
    • =TEXTAFTER(A2,"@") gives everything after it.

    The delimiter goes in double quotes. It can be more than one character, such as ", ".

  • What if the character appears more than once?

    Add a third part to say which one. =TEXTAFTER(A2,".",-1) splits at the last dot, which gives the extension of a file name like report.final.xlsx. A positive number counts from the start, a negative one from the end.

  • What if the character isn't there?

    You get #N/A. Wrap it in IFNA, or give TEXTBEFORE its sixth part, the value to show instead.

  • How did people do this before?

    With LEFT, MID and FIND: =LEFT(A2,FIND("@",A2)-1). That still works everywhere, but TEXTBEFORE and TEXTAFTER (Excel 365 and Google Sheets) are much easier to read.

Know someone fighting with a spreadsheet? Send them this page.