Excel Practice
Lessons Lesson 69

TEXTAFTER

About this lesson

TEXTAFTER returns the part of a text after a delimiter you name. An instance of -1 makes it the part after the last occurrence.

By the end you can

  • take the part of a cell after a delimiter with TEXTAFTER.
  • take the part after the last delimiter with -1.

Practises TEXTAFTER TEXTBEFORE RIGHT FIND

The idea

The other half of TEXTBEFORE, with one argument that matters: the instance. Left out, TEXTAFTER stops at the first delimiter. That is right for an email address and wrong for report.final.xlsx. Set it to -1 and it stops at the last one. That is what a file type, a surname and the last part of a path all need.

The mistake is the space. Splitting 12 High St, Leeds on a comma gives Leeds with a leading space that a lookup will not match. Make the delimiter the comma and the space together, or wrap the result in TRIM.

The mistake to watch for

Leaving the instance out on text with more than one delimiter. TEXTAFTER stops at the first dot. So report.final.xlsx gives final.xlsx instead of the file type. -1 stops at the last. The second mistake is the space. Splitting an address on a bare comma leaves a leading space on the city that a lookup will not match. Make the delimiter the comma and the space together.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In B2, take the domain: the part of the email address after the @ sign.

To begin, type it exactly:

=TEXTAFTER(A2,"@")

B2
Row A B C D E F G
1 Email Domain File Type
2 ada.lovelace@example.com report.final.xlsx
3 ben@mail.example.org photo.jpg
4 cai.n@corp.example.com notes.v2.docx
5
6 Address City
7 12 High St, Leeds
8 4 Mill Lane, York

Every step

  1. 1

    In B2, take the domain: the part of the email address after the @ sign. To begin, type it exactly: =TEXTAFTER(A2,"@").

    Hint. The cell, then "@".

  2. 2

    File names can hold more than one dot. In E2, take the file type after the last dot, using -1 as the instance.

    Hint. Instance -1.

  3. 3

    Read =TEXTAFTER(D2,".") and say what it returns. No instance this time.

    Hint. After the first dot.

  4. 4

    In B7, take the city from the address: everything after the comma and the space.

    Hint. Comma and space, in one pair of quotation marks.

  5. 5

    In E4, write a formula using TEXTAFTER that gives the type of the file in D4.

    Hint. Last dot.