TEXTBEFORE returns the portion of a text string that appears before a specified delimiter. It lets you split names, codes, and labels cleanly without complex formulas.
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])What does TEXTBEFORE do?
TEXTBEFORE is a text function that extracts the characters to the left of a delimiter. You give it a text value and a delimiter such as a comma, hyphen, or space, and it returns everything before that delimiter. Optional arguments let you pick which occurrence to use, ignore case, treat the end of the text as a delimiter, or supply a fallback value when the delimiter is missing.
Before TEXTBEFORE, splitting text usually meant combining LEFT, FIND, and IFERROR, which was hard to read and easy to break. TEXTBEFORE replaces that with one clear formula. It is especially useful when data arrives as a single column of combined values, such as "Lastname, Firstname" or "SKU-1234", and you need one part in its own column for sorting, matching, or reporting.
Imagine a monthly export where customer names arrive as "Smith, John" in one column. You need the surname in its own column to sort alphabetically. With TEXTBEFORE you write one formula, fill it down, and the surnames appear instantly. The same pattern works for order codes, email addresses, file paths, and any other delimited text.
When to use TEXTBEFORE
- Extract first names or surnames from "Last, First" name lists.
- Pull the product code from labels like "SKU-1234-Blue".
- Get the username from an email address before the @ sign.
- Separate the area code from a phone number formatted with hyphens.
- Isolate the folder name from a file path before a backslash.
- Split a combined address into street and city parts.
TEXTBEFORE syntax
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])| Argument | Required? | What to enter |
|---|---|---|
text | Required | The text value or cell reference you want to split. Example: A2 containing "Smith, John". |
delimiter | Required | The character or characters that mark where to stop. Example: ", " to split at a comma and space. |
instance_num | Optional | Which occurrence of the delimiter to use. Default is 1 (the first). Use 2 to split at the second occurrence. Example: 2. |
match_mode | Optional | Set to 0 for case-sensitive matching (default) or 1 for case-insensitive. Example: 1 ignores case. |
match_end | Optional | Set to 1 to treat the end of the text as a delimiter, useful when the delimiter may be missing. Default is 0. Example: 1. |
if_not_found | Optional | The value to return when the delimiter is not found. Without it, Excel returns #N/A. Example: "Not found". |
What it returns: TEXTBEFORE returns a text string containing the characters before the chosen delimiter. If the delimiter is not found and no fallback is supplied, it returns the #N/A error.
TEXTBEFORE examples
1 Extract the first name from a full name
The goal is to get the first name from a column of full names separated by a space. The formula =TEXTBEFORE(A2, " ") looks in cell A2 for the first space and returns everything before it. For "John Smith" it returns "John". Fill the formula down to split every name in the list.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full Name | |||
| 2 | John Smith | John | ||
| 3 | Maria Garcia | |||
| 4 | David Chen | |||
| 5 | Sarah Johnson | |||
| 6 | Ahmed Hassan | |||
| 7 |
2 Get the surname from "Last, First" format
Here names are stored as "Smith, John" with a comma and space between the surname and first name. The formula =TEXTBEFORE(A2, ", ") finds the first comma followed by a space and returns the text before it. For "Smith, John" it returns "Smith". This is ideal for sorting by surname.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | |||
| 2 | Smith, John | Smith | ||
| 3 | Garcia, Maria | |||
| 4 | Chen, David | |||
| 5 | Johnson, Sarah | |||
| 6 | Hassan, Ahmed | |||
| 7 |
3 Pull the product code from a SKU label
Product labels look like "SKU-1234-Blue" and you need the code before the first hyphen. The formula =TEXTBEFORE(A2, "-") returns everything before the first hyphen. For "SKU-1234-Blue" it returns "SKU". If you need the number instead, use instance_num 2 to split at the second hyphen.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Label | |||
| 2 | SKU-1234-Blue | SKU | ||
| 3 | SKU-5678-Red | |||
| 4 | SKU-9012-Green | |||
| 5 | SKU-3456-Yellow | |||
| 6 | SKU-7890-Black | |||
| 7 |
4 Extract the username from an email address
Email addresses contain a username before the @ sign. The formula =TEXTBEFORE(A2, "@") returns the text before the first @. For "john.smith@example.com" it returns "john.smith". This is useful for building login names or matching records.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | john.smith@example.com | john.smith | ||
| 3 | maria.garcia@company.org | |||
| 4 | david.chen@school.edu | |||
| 5 | sarah.johnson@business.net | |||
| 6 | ahmed.hassan@service.co | |||
| 7 |
5 Split a phone number into area code and local number
Phone numbers are stored as "555-123-4567" and you need the area code. The formula =TEXTBEFORE(A2, "-") returns the text before the first hyphen. For "555-123-4567" it returns "555". To get the local number, use instance_num 2 with a different approach, but here the goal is the area code.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Phone | |||
| 2 | 555-123-4567 | 555 | ||
| 3 | 212-987-6543 | |||
| 4 | 415-555-0198 | |||
| 5 | 305-444-7777 | |||
| 6 | 617-333-2222 | |||
| 7 |
Common TEXTBEFORE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The delimiter was not found in the text and no if_not_found value was supplied. | Add a fallback value as the sixth argument, for example =TEXTBEFORE(A2, "-", 1, 0, 0, "Not found"). |
| #VALUE! | The instance_num argument is less than 1 or not a number. | Use a whole number of 1 or greater for instance_num, or leave it blank to use the default of 1. |
| #VALUE! | The delimiter is an empty string (""). | Provide a non-empty delimiter such as a space, comma, or hyphen. |
| #NAME? | The function name is misspelled or the version of Excel does not support TEXTBEFORE. | Check the spelling and ensure you are using Excel 365 or Excel 2021 or later. For older versions, use LEFT with FIND. |
| #N/A | The instance_num is greater than the number of times the delimiter appears. | Reduce instance_num to a number that exists in the text, or add an if_not_found fallback. |
Tips for using TEXTBEFORE
- Use instance_num to pick a later delimiter, such as the second hyphen in a code.
- Set match_mode to 1 when the delimiter might appear in different cases, such as "X" and "x".
- Set match_end to 1 to treat the end of the text as a delimiter, which avoids errors when the delimiter is missing.
- Always add an if_not_found value in reports so missing delimiters do not break your calculations.
- Combine TEXTBEFORE with TEXTJOIN or VALUE to convert extracted text into numbers when needed.
- Test the formula on a few rows before filling it down to the whole column.
Frequently asked questions
What is the difference between TEXTBEFORE and TEXTAFTER?
TEXTBEFORE returns the text before a delimiter, while TEXTAFTER returns the text after it. They are often used together to split a value into two parts.
Can TEXTBEFORE return a number instead of text?
TEXTBEFORE always returns text. To use the result in calculations, wrap it with VALUE, for example =VALUE(TEXTBEFORE(A2, "-")).
Why do I get #N/A with TEXTBEFORE?
The delimiter was not found in the text. Add an if_not_found value as the sixth argument or check that the delimiter matches exactly.
Does TEXTBEFORE work in older versions of Excel?
No, TEXTBEFORE is available in Excel 365 and Excel 2021 and later. In older versions, use LEFT with FIND as a workaround.
Can I use TEXTBEFORE with multiple delimiters?
TEXTBEFORE accepts only one delimiter string. To split on several characters, use a helper column or a more advanced formula with TEXTSPLIT.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. TEXTBEFORE reference