TEXTBEFORE & TEXTAFTER

Freebeginner 15 min4 tasks0/66 pts

TEXTBEFORE returns the text before a delimiter and TEXTAFTER the text after it. They replace the LEFT/FIND and MID/FIND combos with one short function. Use a negative instance number to count from the end.

Syntax

TEXTBEFORE(text, delimiter, [instance_num]) · TEXTAFTER(text, delimiter, [instance_num])

Example: =TEXTBEFORE("ana@mail.com","@") returns ana. =TEXTAFTER("a/b/c.xlsx","/",-1) returns c.xlsx.

Common mistakes

Forgetting these need a recent Excel version (Microsoft 365 or Excel 2024). In Excel 2021 and earlier they show #NAME?, so use LEFT/MID with FIND there.
Expecting a match when the delimiter is not in the text: you get #N/A.

SUBSTITUTE Function TRIM & CLEAN Functions

Topics/Text Functions

TEXTBEFORE & TEXTAFTER

Freebeginner 15 min4 tasks0/66 pts

Syntax

TEXTBEFORE(text, delimiter, [instance_num]) · TEXTAFTER(text, delimiter, [instance_num])

Example: =TEXTBEFORE("ana@mail.com","@") returns ana. =TEXTAFTER("a/b/c.xlsx","/",-1) returns c.xlsx.

Common mistakes

Forgetting these need a recent Excel version (Microsoft 365 or Excel 2024). In Excel 2021 and earlier they show #NAME?, so use LEFT/MID with FIND there.
Expecting a match when the delimiter is not in the text: you get #N/A.

SUBSTITUTE Function TRIM & CLEAN Functions