TEXTBEFORE Function Excel

What this does

These two return everything before or after a delimiter. They exist to retire one of the most-copied formulas in spreadsheet history: =LEFT(A2, FIND("@", A2)-1), with its off-by-one that everybody gets wrong at least once. The instance argument picks which occurrence to split at, and a negative value counts from the end — so getting the file extension after the LAST dot is one argument rather than a nested reversal trick. The if_not_found argument replaces the IFERROR wrapper the old idiom always needed. Requires Excel 365. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “textbefore function excel”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a calculation you can defend to a CFO or an auditor, but the practical win is that someone else can open the file and understand what happened without asking you.

A worked example

Splitting an email: =TEXTBEFORE(A2, "@") returns the username and =TEXTAFTER(A2, "@") the domain. A file extension after the final dot: =TEXTAFTER(B2, ".", -1). Handling missing delimiters gracefully: =TEXTBEFORE(A2, "@", 1, 0, 0, A2) returns the original value instead of #N/A. TEXTBEFORE and TEXTAFTER retire the LEFT-with-FIND idiom and its permanent off-by-one, which is worth the version requirement alone. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “textbefore function excel”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.

Common mistakes

• Using them in Excel 2021 or earlier, where they return #NAME? — the LEFT/FIND idiom is still the portable option.
• Omitting if_not_found, so any row missing the delimiter returns #N/A and breaks the column.
• Forgetting that the delimiter is case-sensitive by default; the match_mode argument relaxes it.

Frequently asked questions