The TEXTBEFORE function was officially introduced with Excel 2021, but thanks to Modern Functions Update (MFU) you can now run it on your Excel 2007, 2010, 2013, 2016, 2019 and 2021 versions. It works exactly as its original Microsoft counterpart.
Description
The TEXTBEFORE function extracts all text that occurs before a specified delimiter or substring. It’s the perfect tool when you need to get everything to the left of a certain character, word, or pattern.
Think of it as the LEFT function on steroids. While LEFT requires you to know exactly how many characters to extract, TEXTBEFORE works with whatever text comes before your delimiter.
This is particularly useful for:
- Extracting names: Get first name from “John, Doe”
- Parsing email addresses: Extract the username from “user@domain.com”
- Cleaning product codes: Get the main part before a variant suffix
- Preparing data: Isolate city names from “City, State, Zip”
- Processing imports: Extract key information from structured text
Syntax
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
| Argument | Description |
|---|---|
text | Required. The text string to extract from. |
delimiter | Required. The character(s) that mark where to stop extracting. |
[instance_num] | Optional. Which occurrence of the delimiter to use. Positive counts from left, negative from right. Default is 1. |
[match_mode] | Optional. Case-sensitivity: 0 = case-sensitive (default), 1 = case-insensitive. |
[match_end] | Optional. Treat end of text as delimiter: 0 = disabled (default), 1 = enabled. |
[if_not_found] | Optional. Value to return when no delimiter is found. Default is #N/A. |
Examples
Basic Usage
To extract the name from “John Doe” using a space as delimiter:
=TEXTBEFORE("John Doe", " ")
Returns: "John"
With Multiple Delimiters
To handle different styles of email formatting where the comma appears with and without a space:
=TEXTBEFORE("Smith, John", {",", ", "})
Returns: "Smith"
The array constant {",", ", "} tells Excel to look for either delimiter.
Positive Instance Number (Counting from Left)
For the text “ABX-112-Red-Y”:
=TEXTBEFORE("ABX-112-Red-Y", "-", 1)
Returns: "ABX" (before the first hyphen)
=TEXTBEFORE("ABX-112-Red-Y", "-", 2)
Returns: "ABX-112" (before the second hyphen)
Negative Instance Number (Counting from Right)
To extract text before the last delimiter:
=TEXTBEFORE("ABX-112-Red-Y", "-", -1)
Returns: "ABX-112-Red" (before the last hyphen)
=TEXTBEFORE("ABX-112-Red-Y", "-", -2)
Returns: "ABX-112" (before the second to last hyphen)
Case-Insensitive Search
By default, TEXTBEFORE is case-sensitive. To ignore case:
=TEXTBEFORE("Width X Height", " x ", , 1)
Returns: "Width" (finds “X” even though we searched for “x”)
The match_mode argument (1) tells Excel to ignore case.
Handling “Not Found”
By default, TEXTBEFORE returns #N/A if the delimiter is not found. To return a custom message:
=TEXTBEFORE(A1, "@", , , , "No delimiter found")
Returns the text before “@” or "No delimiter found" if none exists.
Using Cell References
If A1 contains “apple-orange-grape”:
=TEXTBEFORE(A1, "-", 2)
Returns: "apple-orange"
TEXTBEFORE vs Traditional Functions
| Feature | TEXTBEFORE | Traditional (LEFT, MID, SEARCH) |
|---|---|---|
| Extract before delimiter | ✅ Simple syntax | ❌ Complex nesting required |
| Find nth occurrence | ✅ Built-in | ❌ Requires complex formulas |
| Reverse search | ✅ Negative instance numbers | ❌ Not easily achievable |
| Case-insensitive | ✅ Built-in | ❌ Requires extra logic |
| Dynamic updates | ✅ Yes | ✅ Yes |
Practical Use Cases
| Scenario | Formula |
|---|---|
| Extract first name | =TEXTBEFORE(A1, " ") |
| Get username from email | =TEXTBEFORE(A1, "@") |
| Extract city from “City, State” | =TEXTBEFORE(A1, ",") |
| Get text before last space | =TEXTBEFORE(A1, " ", -1) |
| Extract before second occurrence | =TEXTBEFORE(A1, "-", 2) |
| Case-insensitive search | =TEXTBEFORE(A1, "x", , 1) |
| Handle missing delimiter | =TEXTBEFORE(A1, "@", , , , "Not found") |
Notes
- ✅ Fully tested – The implementation in Modern Functions Update works identically to Microsoft’s original function introduced in Excel 2024/Microsoft 365.
- ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
- ℹ️
TEXTBEFOREis case-sensitive by default. Usematch_mode = 1to ignore case. - ℹ️ A negative
instance_numstarts searching from the end of the text. - ℹ️
TEXTBEFOREalways returns a text string, even if the result looks like a number. - ⚠️ If
instance_numis out-of-range,TEXTBEFOREreturns#N/A.