The TEXTBEFORE function in Excel extracts text that appears before a specified character, word, or delimiter. It is useful for separating employee IDs, email usernames, product codes, names, and other business data. In this tutorial, you will learn how to use TEXTBEFORE with practical examples, understand its arguments, and practise extracting text from real-world records.
TEXTBEFORE Function Syntax
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Arguments:
- text: The original text or cell reference.
- delimiter: The character or text before which you want to extract information.
- instance_num: Specifies which occurrence of the delimiter to use. The default is 1. A negative number searches from the end.
- match_mode: 0 performs a case-sensitive match; 1 performs a case-insensitive match.
- match_end: TRUE treats the end of the text as a delimiter. FALSE uses normal delimiter matching.
- if_not_found: Specifies what Excel should return when the delimiter is not found.
Note: TEXTBEFORE is available in Microsoft 365 and Excel 2024.
Question 1: Extract the First Name from a Full Name
Problem Statement
An HR department stores employees’ full names in a single column. Extract only the first name from each record.
| Full Name |
|---|
| Rahul Sharma |
| Priya Verma |
| Amit Kumar |
| Neha Singh |
Assume the names are in cells A2.
Excel Formula
=TEXTBEFORE(A2," ")
Expected Result
| Full Name | First Name |
|---|---|
| Rahul Sharma | Rahul |
| Priya Verma | Priya |
| Amit Kumar | Amit |
| Neha Singh | Neha |
Explanation: The space character is the delimiter. TEXTBEFORE returns everything appearing before the first space.
Student Task: Extract the first names from these records:
| Full Name |
|---|
| Karan Mehta |
| Simran Kapoor |
New Expected Result
| First Name |
|---|
| Karan |
| Simran |
Question 2: Extract the Department Code from Employee IDs
Problem Statement
A company maintains employee IDs containing a department code followed by an employee number. Extract only the department code.
| Employee ID |
|---|
| HR-101 |
| IT-205 |
| FIN-310 |
| MKT-415 |
Assume employee IDs are in cells A2.
Excel Formula
=TEXTBEFORE(A2,"-")
Expected Result
| Employee ID | Department Code |
|---|---|
| HR-101 | HR |
| IT-205 | IT |
| FIN-310 | FIN |
| MKT-415 | MKT |
Explanation: The hyphen (-) separates the department code from the employee number. TEXTBEFORE extracts everything before the hyphen.
Student Task: Extract department codes from these employee IDs:
| Employee ID |
|---|
| ACC-520 |
| ADM-625 |
New Expected Result
| Department Code |
|---|
| ACC |
| ADM |
Question 3: Extract the Username from an Email Address
Problem Statement
A company maintains employee email addresses. Extract only the username appearing before the @ symbol.
| Email Address |
|---|
| rahul@gmail.com |
| priya@yahoo.com |
| amit@company.in |
| neha@outlook.com |
Assume email addresses are in cells A2.
Excel Formula
=TEXTBEFORE(A2,"@")
Expected Result
| Email Address | Username |
|---|---|
| rahul@gmail.com | rahul |
| priya@yahoo.com | priya |
| amit@company.in | amit |
| neha@outlook.com | neha |
Explanation: The @ symbol marks the boundary between the username and email domain. TEXTBEFORE returns the text before that symbol.
Student Task: Extract the usernames from these email addresses:
| Email Address |
|---|
| karan@vsit.in |
| simran@codermantra.com |
New Expected Result
| Username |
|---|
| karan |
| simran |
Question 4: Extract the Product Name from Product Codes
Problem Statement
An inventory department stores product names and product codes together, separated by an underscore. Extract only the product name.
| Product Code |
|---|
| Laptop_DELL101 |
| Keyboard_LOG205 |
| Monitor_HP310 |
| Mouse_LEN415 |
Assume product codes are in cells A2.
Excel Formula
=TEXTBEFORE(A2,"_")
Expected Result
| Product Code | Product Name |
|---|---|
| Laptop_DELL101 | Laptop |
| Keyboard_LOG205 | Keyboard |
| Monitor_HP310 | Monitor |
| Mouse_LEN415 | Mouse |
Explanation: The underscore (_) acts as the delimiter. TEXTBEFORE extracts the product name before the underscore.
Student Task: Extract the product names from these codes:
| Product Code |
|---|
| Printer_CAN520 |
| Scanner_HP625 |
New Expected Result
| Product Name |
|---|
| Printer |
| Scanner |
Question 5: Extract Text Before the Second Delimiter
Problem Statement
A company maintains product codes containing multiple sections. Extract the information appearing before the second hyphen.
| Product Code |
|---|
| ELEC-LAP-101 |
| FURN-CHR-205 |
| ACC-MOU-310 |
| ELEC-MON-415 |
Assume product codes are in cells A2.
Excel Formula
=TEXTBEFORE(A2,"-",2)
Expected Result
| Product Code | Extracted Text |
|---|---|
| ELEC-LAP-101 | ELEC-LAP |
| FURN-CHR-205 | FURN-CHR |
| ACC-MOU-310 | ACC-MOU |
| ELEC-MON-415 | ELEC-MON |
Explanation: The third argument is set to 2, so TEXTBEFORE searches for the second occurrence of the hyphen and returns everything before it.
Student Task: Extract text before the second hyphen:
| Product Code |
|---|
| IT-SRV-520 |
| HR-EMP-625 |
New Expected Result
| Extracted Text |
|---|
| IT-SRV |
| HR-EMP |
Question 6: Extract Text Before the Last Delimiter
Problem Statement
A company stores product information in a multi-section format. Extract everything before the last hyphen.
| Product Information |
|---|
| INDIA-DELHI-101 |
| INDIA-MUMBAI-205 |
| INDIA-NOIDA-310 |
| INDIA-GURUGRAM-415 |
Assume the information is in cells A2.
Excel Formula
=TEXTBEFORE(A2,"-",-1)
Expected Result
| Product Information | Extracted Text |
|---|---|
| INDIA-DELHI-101 | INDIA-DELHI |
| INDIA-MUMBAI-205 | INDIA-MUMBAI |
| INDIA-NOIDA-310 | INDIA-NOIDA |
| INDIA-GURUGRAM-415 | INDIA-GURUGRAM |
Explanation: The instance number -1 tells Excel to search from the end and use the last hyphen as the delimiter.
Student Task: Extract everything before the last underscore:
| File Information |
|---|
| SALES_NORTH_2025 |
| SALES_SOUTH_2026 |
Excel Formula for Student Practice
=TEXTBEFORE(A2,"_",-1)
New Expected Result
| Extracted Text |
|---|
| SALES_NORTH |
| SALES_SOUTH |
Question 7: Extract Text Before a Specific Word
Problem Statement
A training institute stores course descriptions in a single column. Extract the text appearing before the word Course.
| Course Description |
|---|
| Python Course for Beginners |
| Excel Course for Professionals |
| Java Course for Students |
| PHP Course for Developers |
Assume descriptions are in cells A2.
Excel Formula
=TEXTBEFORE(A2," Course")
Expected Result
| Course Description | Extracted Text |
|---|---|
| Python Course for Beginners | Python |
| Excel Course for Professionals | Excel |
| Java Course for Students | Java |
| PHP Course for Developers | PHP |
Explanation: The delimiter includes a space followed by the word Course. This extracts the course name without leaving an extra space at the end.
Student Task: Extract the text before Training:
| Training Description |
|---|
| Advanced Excel Training Program |
| Python Training Program |
New Expected Result
| Extracted Text |
|---|
| Advanced Excel |
| Python |
Question 8: Extract Text Before a Delimiter Without Case Sensitivity
Problem Statement
A company receives department records where the word Department may appear in uppercase or lowercase. Extract the text before that word without considering its case.
| Department Information |
|---|
| Sales Department |
| Finance department |
| IT DEPARTMENT |
| HR Department |
Assume records are in cells A2.
Excel Formula
=TEXTBEFORE(A2," department",,1)
Expected Result
| Department Information | Extracted Text |
|---|---|
| Sales Department | Sales |
| Finance department | Finance |
| IT DEPARTMENT | IT |
| HR Department | HR |
Explanation: The fourth argument is set to 1, making delimiter matching case-insensitive. Excel recognizes Department, department, and DEPARTMENT as matching text.
Student Task: Extract text before the word branch using case-insensitive matching:
| Branch Information |
|---|
| Delhi Branch |
| Mumbai branch |
| Noida BRANCH |
New Expected Result
| Extracted Text |
|---|
| Delhi |
| Mumbai |
| Noida |
Question 9: Return a Custom Message When the Delimiter Is Missing
Problem Statement
An HR department receives employee records. Some records contain a hyphen, while others do not. Extract the department code before the hyphen. If the hyphen is missing, display a custom message.
| Employee ID |
|---|
| HR-101 |
| IT-205 |
| FIN310 |
| MKT-415 |
Assume employee IDs are in cells A2.
Excel Formula
=TEXTBEFORE(A2,"-",,,,"Delimiter Not Found")
Expected Result
| Employee ID | Department Code |
|---|---|
| HR-101 | HR |
| IT-205 | IT |
| FIN310 | Delimiter Not Found |
| MKT-415 | MKT |
Explanation: The sixth argument specifies the value Excel should return when the delimiter is not found. In this example, it displays Delimiter Not Found instead of an error.
Student Task: Extract the text before the underscore. If the underscore is missing, display No Separator.
| Product Code |
|---|
| LAPTOP_DELL |
| MOUSE_LOG |
| MONITORHP |
Excel Formula for Student Practice
=TEXTBEFORE(A2,"_",,,, "No Separator")
New Expected Result
| Extracted Text |
|---|
| LAPTOP |
| MOUSE |
| No Separator |
Question 10: Extract Text Before the End of a Text String
Problem Statement
A company stores file names with and without a final separator. Extract the text before a hyphen, treating the end of the text as a delimiter when the hyphen is missing.
| File Name |
|---|
| Report-2025 |
| Invoice-2026 |
| Summary |
| Employee-Details |
Assume file names are in cells A2.
Excel Formula
=TEXTBEFORE(A2,"-",,0,TRUE)
Expected Result
| File Name | Extracted Text |
|---|---|
| Report-2025 | Report |
| Invoice-2026 | Invoice |
| Summary | Summary |
| Employee-Details | Employee |
Explanation: The fifth argument is TRUE, so Excel also treats the end of the text as a delimiter. When the hyphen is missing, the complete text is returned.
Student Task: Extract text before the underscore or return the complete text if the underscore is missing.
| File Name |
|---|
| SALES_NORTH |
| HR_EMPLOYEE |
| FINANCE |
Excel Formula for Student Practice
=TEXTBEFORE(A2,"_",,0,TRUE)
New Expected Result
| Extracted Text |
|---|
| SALES |
| HR |
| FINANCE |
Student Practice Tasks
Try these exercises independently using only the TEXTBEFORE function.
- Extract the first name from
Aarav Sharma. - Extract the department code from
IT-205. - Extract the username from
support@company.com. - Extract the product name from
Laptop_DELL101. - Extract text before the second hyphen in
INDIA-DELHI-101. - Extract everything before the last underscore in
SALES_NORTH_2025. - Extract text before the word
CourseinPython Course for Beginners. - Extract text before
departmentusing case-insensitive matching. - Display
Not Foundwhen a hyphen is missing. - Return the complete text when the specified delimiter is missing by using the match_end argument.
Final Student Challenge: Employee Record Extraction
Problem Statement
A company maintains employee records in a combined format. Each record contains a department code, employee number, and location, separated by hyphens.
Your task is to extract the text before the second hyphen for every employee.
Given Data
| Employee Record |
|---|
| HR-101-DELHI |
| IT-205-MUMBAI |
| FIN-310-NOIDA |
| MKT-415-GURUGRAM |
Assume the employee records are in cells A2.
Your Task
Extract the department code and employee number together, leaving the location out of the result.
Excel Formula
=TEXTBEFORE(A2,"-",2)
Expected Result
| Employee Record | Extracted Information |
|---|---|
| HR-101-DELHI | HR-101 |
| IT-205-MUMBAI | IT-205 |
| FIN-310-NOIDA | FIN-310 |
| MKT-415-GURUGRAM | MKT-415 |
Explanation: The instance number 2 tells TEXTBEFORE to find the second hyphen. Everything before that delimiter is returned.
Key Takeaways
- TEXTBEFORE extracts text appearing before a specified delimiter.
- The delimiter can be a space, hyphen, comma, underscore, or word.
- The instance_num argument selects a particular occurrence of the delimiter.
- A negative instance number searches from the end.
- The match_mode argument controls case-sensitive or case-insensitive matching.
- The match_end argument can treat the end of the text as a delimiter.
- The if_not_found argument displays a custom result when the delimiter is missing.
- TEXTBEFORE is useful for extracting employee IDs, email usernames, product names, and business information.
Frequently Asked Questions (FAQs)
1. What is the TEXTBEFORE function in Excel?
TEXTBEFORE is an Excel text function that extracts everything appearing before a specified character, word, or delimiter.
2. What is the syntax of TEXTBEFORE?
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
3. Can TEXTBEFORE extract text before a space?
Yes. Use a space as the delimiter:
=TEXTBEFORE(A2," ")
4. How can I extract text before the second hyphen?
Set the instance number to 2:
=TEXTBEFORE(A2,"-",2)
5. Can TEXTBEFORE in Excel search from the end of a text string?
Yes. Use a negative instance number to search from the end:
=TEXTBEFORE(A2,"-",-1)
6. Is TEXTBEFORE case-sensitive?
By default, matching is case-sensitive. Set match_mode to 1 to make delimiter matching case-insensitive.
7. What happens when the delimiter is not found?
TEXTBEFORE normally returns a #N/A error. You can specify a custom result using the if_not_found argument.
8. What is the use of the match_end argument?
The match_end argument allows Excel to treat the end of the text as a delimiter when set to TRUE.
9. Is TEXTBEFORE available in all Excel versions?
No. TEXTBEFORE is available in Microsoft 365 and Excel 2024. It may not work in older Excel versions.
10. What is the difference between TEXTBEFORE and TEXTAFTER?
TEXTBEFORE extracts text appearing before a specified delimiter, while TEXTAFTER extracts text appearing after that delimiter.

