Excel Practice
Lessons Lesson 68

TEXTBEFORE

About this lesson

TEXTBEFORE returns the part of a text before a delimiter you name. An optional third argument says which occurrence of the delimiter to stop at.

By the end you can

  • take the part of a cell before a delimiter with TEXTBEFORE.
  • pick the second occurrence, and handle a delimiter that is missing.

Practises TEXTBEFORE TEXTAFTER LEFT FIND IFNA

The idea

This is LEFT with FIND from the extracting module, folded into one function. The old way was LEFT(A2, FIND("@",A2)-1), with its minus one that everybody forgets. The new way is TEXTBEFORE(A2,"@"). The third argument chooses the occurrence: 2 for the second delimiter, -1 for the last.

The difference that matters is the failure. LEFT with FIND gives #VALUE! when the delimiter is missing. TEXTBEFORE gives #N/A, the same error as a lookup that misses, and the same IFNA wraps it. TEXTBEFORE is in Excel 2024 and Microsoft 365. A file that has to open in older versions still needs the long way.

The mistake to watch for

Forgetting that the delimiter can be missing. TEXTBEFORE returns #N/A when the character is not there. So one address without an @ sign turns a column red. It is the same error a lookup gives for a missing value, and the same IFNA wraps it. And the instance argument counts occurrences. 2 stops at the second delimiter and keeps the first one in the result.

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 part of the email address before the @ sign, with TEXTBEFORE.

To begin, type it exactly:

=TEXTBEFORE(A2,"@")

B2
Row A B C D E F G
1 Email User Code Prefix Family
2 ada.lovelace@example.com AB-1042-X
3 ben@mail.example.org CD-77-Y
4 cai.n@corp.example.com EF-3-Z
5 dee.example.com
6
7
8

Every step

  1. 1

    In B2, take the part of the email address before the @ sign, with TEXTBEFORE. To begin, type it exactly: =TEXTBEFORE(A2,"@").

    Hint. The cell, then "@".

  2. 2

    Read =TEXTBEFORE(A3,"@") and say what it returns.

    Hint. Up to the @.

  3. 3

    The product codes have three parts joined by hyphens. In E2, take the prefix: everything before the first hyphen.

    Hint. The delimiter is "-".

  4. 4

    In F2, take everything before the second hyphen instead. Use TEXTBEFORE's third argument to say which occurrence you mean.

    Hint. A third argument: 2.

  5. 5

    B5 tries to split A5, which has no @ sign in it, and shows #N/A. Wrap it in IFNA so the cell reads No @ instead.

    Hint. IFNA, with the split inside.