TEXTBEFORE Function in Excel Tutorial

Excel Tutorial

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 NameFirst Name
Rahul SharmaRahul
Priya VermaPriya
Amit KumarAmit
Neha SinghNeha

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 IDDepartment Code
HR-101HR
IT-205IT
FIN-310FIN
MKT-415MKT

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 AddressUsername
rahul@gmail.comrahul
priya@yahoo.compriya
amit@company.inamit
neha@outlook.comneha

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 CodeProduct Name
Laptop_DELL101Laptop
Keyboard_LOG205Keyboard
Monitor_HP310Monitor
Mouse_LEN415Mouse

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 CodeExtracted Text
ELEC-LAP-101ELEC-LAP
FURN-CHR-205FURN-CHR
ACC-MOU-310ACC-MOU
ELEC-MON-415ELEC-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 InformationExtracted Text
INDIA-DELHI-101INDIA-DELHI
INDIA-MUMBAI-205INDIA-MUMBAI
INDIA-NOIDA-310INDIA-NOIDA
INDIA-GURUGRAM-415INDIA-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 DescriptionExtracted Text
Python Course for BeginnersPython
Excel Course for ProfessionalsExcel
Java Course for StudentsJava
PHP Course for DevelopersPHP

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 InformationExtracted Text
Sales DepartmentSales
Finance departmentFinance
IT DEPARTMENTIT
HR DepartmentHR

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 IDDepartment Code
HR-101HR
IT-205IT
FIN310Delimiter Not Found
MKT-415MKT

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 NameExtracted Text
Report-2025Report
Invoice-2026Invoice
SummarySummary
Employee-DetailsEmployee

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.

  1. Extract the first name from Aarav Sharma.
  2. Extract the department code from IT-205.
  3. Extract the username from support@company.com.
  4. Extract the product name from Laptop_DELL101.
  5. Extract text before the second hyphen in INDIA-DELHI-101.
  6. Extract everything before the last underscore in SALES_NORTH_2025.
  7. Extract text before the word Course in Python Course for Beginners.
  8. Extract text before department using case-insensitive matching.
  9. Display Not Found when a hyphen is missing.
  10. 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 RecordExtracted Information
HR-101-DELHIHR-101
IT-205-MUMBAIIT-205
FIN-310-NOIDAFIN-310
MKT-415-GURUGRAMMKT-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.