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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

TEXTAFTER returns the text after a delimiter you name.

In B2, type:

=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

    TEXTAFTER returns the text after a delimiter you name. In B2, type: =TEXTAFTER(A2,"@").

    Hint. The cell, then "@".

  2. 2

    File names can have more than one dot. In E2, take the file type after the last dot. Shape: =TEXTAFTER(cell,"delimiter",-1).

    Hint. Use -1 as the instance.

  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 after the comma and space. Remember: a delimiter can be more than one character.

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

  5. 5

    In E4, find the file type of D4 with TEXTAFTER. D4 has more than one dot.

    Hint. Last dot.