Text
TEXTBEFORE Function in Excel
Returns text that occurs before a specified delimiter.
Syntax
- =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Arguments
- text (required): Text to search in
- delimiter (required): Text to search for
- instance_num (optional): Which occurrence (default 1)
Examples
- =TEXTBEFORE("John Smith", " ") - Get first name - Result: John
- =TEXTBEFORE("[email protected]", "@") - Get username - Result: a
Excel TEXTBEFORE function documentation
- TEXTBEFORE returns text before a delimiter — Microsoft 365 text function.
- Syntax: =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]).
- Replaces LEFT+FIND patterns for emails, URLs, codes, and tagged strings.
- Related: [TEXTAFTER](/functions/textafter/), [TEXTSPLIT](/functions/textsplit/), [FIND](/functions/find/).
TEXTBEFORE syntax and instance_num
- text: source string.
- delimiter: substring to search — single char or multi-char.
- instance_num: which occurrence (1 = first before first delimiter).
- if_not_found: custom output when delimiter missing — avoids #VALUE! in audits.
Step-by-step: extract email local part
- Step 1 — Email in A2 [email protected].
- Step 2 — =TEXTBEFORE(A2, "@").
- Step 3 — Domain with [TEXTAFTER](/functions/textafter/)(A2, "@").
- Step 4 — Handle missing @: =TEXTBEFORE(A2, "@", , , , "No email").
- Step 5 — Batch spill down column alongside TEXTAFTER.
TEXTBEFORE vs LEFT vs TEXTSPLIT
- TEXTBEFORE — delimiter-based; length unknown.
- LEFT — fixed character count.
- TEXTSPLIT — all segments at once.
- Legacy: =LEFT(A2, FIND("@",A2)-1) with IFERROR.
Worked examples to copy
- Before dash SKU: =TEXTBEFORE(A2, "-").
- Path root: =TEXTBEFORE(A2, "/", 2) second delimiter instance variants per docs.
- File name: =TEXTBEFORE(A2, ".", -1) last instance in supported builds.
- Clean: =TRIM(TEXTBEFORE(A2, ",")).
People also ask
- TEXTBEFORE Excel 2019? — No; 365 / 2021+.
- Delimiter not found? — Use if_not_found or IFERROR wrapper.
- TEXTBEFORE case sensitive? — match_mode controls case behavior.
- TEXTBEFORE vs REGEXEXTRACT? — TEXTBEFORE simpler for fixed delimiters.
Common errors
- #N/A if delimiter not found
Use cases
- Extract usernames
- Parse names
- Split data
Frequently asked questions
- How do I extract the filename without extension? Use =TEXTBEFORE(filename, ".", -1) where -1 gets text before the LAST period. For "report.2024.xlsx", this returns "report.2024". Use instance_num=1 for text before the first period.
- How do I split a full name into first and last name? First name: =TEXTBEFORE(A1, " "). Last name: =TEXTAFTER(A1, " ", -1) for the part after the last space. For middle names, use TEXTSPLIT to get all parts as an array.
- Can TEXTBEFORE handle multiple delimiters? Yes, pass an array of delimiters: =TEXTBEFORE(A1, {",", ";"}) returns text before whichever delimiter appears first. Useful when data might use different separators.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use TEXTBEFORE
- Extract usernames — common Text scenario for TEXTBEFORE.
- Parse names — common Text scenario for TEXTBEFORE.
- Split data — common Text scenario for TEXTBEFORE.
TEXTBEFORE in the Text category
- Browse all Text functions at /categories/text/ for related formulas.
- TEXTBEFORE syntax: =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
- text (required): Text to search in
- delimiter (required): Text to search for
- instance_num (optional): Which occurrence (default 1)
- Confirm TEXTBEFORE arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm TEXTBEFORE arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- TEXTAFTER (/functions/textafter/): Returns text that occurs after a specified delimiter.
- TEXTSPLIT (/functions/textsplit/): Splits text into rows or columns using delimiters.
- LEFT (/functions/left/): Returns the specified number of characters from the start of a text string.
- FIND (/functions/find/): Finds one text string within another and returns the starting position (case-sensitive).
Comparisons involving this function
- LEFT vs MID vs RIGHT (/compare/left-vs-mid-vs-right/): Use LEFT, MID, and RIGHT for fixed-position extraction. Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in modern Excel when values are separated by a delimiter.
Errors to watch for
- #N/A if delimiter not found — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter TEXTBEFORE on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
TEXTBEFORE worked examples to copy
- =TEXTBEFORE("John Smith", " ") — Get first name. Expected result: John.
- =TEXTBEFORE("[email protected]", "@") — Get username. Expected result: a.
TEXTBEFORE reference summary for crawlers and offline review
- TEXTBEFORE belongs to the Text category in Excel. Returns text that occurs before a specified delimiter.
- Full syntax: =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]). Open /functions/textbefore/ for parameters, FAQs, and related pages.
- Common mistakes: #N/A if delimiter not found
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.