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

By Ali, Founder, Excel Lessons

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.

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

TEXTBEFORE returns the text before a delimiter you name.

In B2, type:

=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

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

    Hint. The cell, then "@".

  2. 2

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

    Hint. Up to the @.

  3. 3

    Product codes have three parts joined by hyphens. In E2, take the text before the first hyphen with TEXTBEFORE. Shape: =TEXTBEFORE(cell,"delimiter").

    Hint. Use "-" as the delimiter.

  4. 4

    TEXTBEFORE's third argument picks which occurrence to stop at. In F2, take everything before the second hyphen. Shape: =TEXTBEFORE(cell,"delimiter",n).

    Hint. A third argument: 2.

  5. 5

    B5 shows #N/A because A5 has no @ sign to split on. Fix B5. Show No @ instead of the error. Remember: IFNA replaces an error with your own text.

    Hint. IFNA, with the split inside.