Introduction
The TEXTAFTER function in Excel extracts text that appears after a specific character, word, or delimiter. It is useful when working with employee IDs, email addresses, product codes, invoice numbers, and other business records.
For example, if a cell contains EMP-HR-105, you can use TEXTAFTER to extract the text after the first hyphen or the last hyphen.
TEXTAFTER Function Syntax
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Arguments:
- text: The original text from which you want to extract data.
- delimiter: The character or text after which you want to extract the remaining text.
- instance_num: Optional. Specifies which occurrence of the delimiter to use. The default is 1. A negative number searches from the end.
- match_mode: Optional. Use
0for case-sensitive matching and1for case-insensitive matching. The default is 0. - match_end: Optional. Use
0for normal matching and1to treat the end of the text as a delimiter. - if_not_found: Optional. Specifies the result to return if the delimiter is not found.
Important Points:
- TEXTAFTER extracts everything after the specified delimiter.
- By default, it searches from the beginning of the text.
- A negative instance number searches from the end.
- TEXTAFTER is available in Microsoft 365 and Excel 2024.
- If the delimiter is not found, Excel returns a
#N/Aerror unless an alternative result is specified.
Question 1: Extract Department Codes from Employee IDs
Problem Statement
An HR department stores employee IDs in a standard format. The team wants to extract the department code appearing after the first hyphen.
| Employee ID | Department Code |
|---|---|
| EMP-HR-105 | HR-105 |
| EMP-IT-208 | IT-208 |
| EMP-FN-310 | FN-310 |
| EMP-MK-405 | MK-405 |
Assume employee IDs are in cells A2.
Excel Formula
=TEXTAFTER(A2,"-")
Expected Result: For EMP-HR-105, the result is HR-105.
Explanation: TEXTAFTER finds the first hyphen and returns everything after it.
Student Task: Enter STAFF-IT-502 in A6 and extract the text after the first hyphen.
New Expected Result: IT-502
Question 2: Extract Email Domains
Problem Statement
A company wants to extract the domain name from employee email addresses.
| Email Address | Domain |
|---|---|
| amit@gmail.com | gmail.com |
| priya@yahoo.com | yahoo.com |
| rahul@company.in | company.in |
| neha@vsit.in | vsit.in |
Assume email addresses are in cells A2.
Excel Formula
=TEXTAFTER(A2,"@")
Expected Result: For amit@gmail.com, the result is gmail.com.
Explanation: The formula finds the @ symbol and returns all text after it.
Student Task: Enter rohit@outlook.com in A6 and extract the email domain.
New Expected Result: outlook.com
Question 3: Extract Product Numbers After a Hyphen
Problem Statement
An inventory department wants to extract the product number appearing after the hyphen in product codes.
| Product Code | Product Number |
|---|---|
| LAP-105 | 105 |
| KEY-205 | 205 |
| MON-310 | 310 |
| USB-450 | 450 |
Assume product codes are in cells A2.
Excel Formula
=TEXTAFTER(A2,"-")
Expected Result: For LAP-105, the result is 105.
Explanation: TEXTAFTER extracts everything after the hyphen.
Student Task: Enter MOU-550 in A6 and extract the product number.
New Expected Result: 550
Question 4: Extract Text After the Last Hyphen
Problem Statement
A company uses employee IDs containing multiple hyphens. The HR department wants to extract only the employee number after the last hyphen.
| Employee ID | Employee Number |
|---|---|
| EMP-HR-1045 | 1045 |
| EMP-IT-2086 | 2086 |
| EMP-FN-3012 | 3012 |
| EMP-MK-4050 | 4050 |
Assume employee IDs are in cells A2.
Excel Formula
=TEXTAFTER(A2,"-",-1)
Expected Result: For EMP-HR-1045, the result is 1045.
Explanation: The instance number -1 tells Excel to search from the end and extract the text after the last hyphen.
Student Task: Enter STAFF-FN-5025 in A6 and extract the employee number after the last hyphen.
New Expected Result: 5025
Question 5: Extract City Names After a Comma
Problem Statement
A company stores employee addresses in a single column. The HR department wants to extract the city name appearing after the comma.
| Employee Address | City |
|---|---|
| Dwarka, New Delhi | New Delhi |
| Sector 15, Gurugram | Gurugram |
| Andheri, Mumbai | Mumbai |
| Salt Lake, Kolkata | Kolkata |
Assume addresses are in cells A2.
Excel Formula
=TEXTAFTER(A2,", ")
Expected Result: For Dwarka, New Delhi, the result is New Delhi.
Explanation: The formula searches for a comma followed by a space and returns the text after it.
Student Task: Enter Sector 7, Noida in A6 and extract the city name.
New Expected Result: Noida
Question 6: Extract Text After the Second Hyphen
Problem Statement
An inventory department uses product codes with multiple hyphens. It wants to extract the text after the second hyphen.
| Product Code | Extracted Text |
|---|---|
| LAP-IT-105 | 105 |
| KEY-HW-205 | 205 |
| MON-DP-310 | 310 |
| USB-AC-450 | 450 |
Assume product codes are in cells A2.
Excel Formula
=TEXTAFTER(A2,"-",2)
Expected Result: For LAP-IT-105, the result is 105.
Explanation: The instance number 2 tells Excel to locate the second hyphen and return everything after it.
Student Task: Enter MOU-WL-550 in A6 and extract the text after the second hyphen.
New Expected Result: 550
Question 7: Extract Text Using Case-Insensitive Matching
Problem Statement
A company stores department information with different letter cases. The HR team wants to extract the text after hr, regardless of whether it appears in uppercase or lowercase.
| Department Record | Search Delimiter | Extracted Text |
|---|---|---|
| EMP-HR-105 | hr | -105 |
| EMP-hr-205 | HR | -205 |
| EMP-Hr-310 | HR | -310 |
| EMP-hR-405 | hr | -405 |
Assume department records are in A2 and the delimiter is in B2.
Excel Formula
=TEXTAFTER(A2,B2,1,1)
Expected Result: For EMP-HR-105, the result is -105.
Explanation: The fourth argument 1 enables case-insensitive matching. The formula can find the delimiter even when its letter case is different.
Student Task: Enter STAFF-hr-505 in A6 and HR in B6. Extract the text after the matching delimiter.
New Expected Result: -505
Student Tasks
Solve the following exercises using only the TEXTAFTER function.
Task 1: Extract Email Domains
| Email Address | Extracted Domain |
|---|---|
| amit@gmail.com | ? |
| priya@yahoo.com | ? |
| rahul@company.in | ? |
| neha@outlook.com | ? |
Your Task: Extract everything after the @ symbol.
Expected Results: gmail.com, yahoo.com, company.in, outlook.com
Task 2: Extract Employee Numbers
| Employee ID | Extracted Number |
|---|---|
| EMP-HR-101 | ? |
| EMP-IT-202 | ? |
| EMP-FN-303 | ? |
| EMP-MK-404 | ? |
Your Task: Extract the text after the last hyphen.
Expected Results: 101, 202, 303, 404
Task 3: Extract City Names
| Employee Address | Extracted City |
|---|---|
| Dwarka, New Delhi | ? |
| Sector 15, Gurugram | ? |
| Andheri, Mumbai | ? |
| Salt Lake, Kolkata | ? |
Your Task: Extract the text after , .
Expected Results: New Delhi, Gurugram, Mumbai, Kolkata
Final Student Challenge: Build an Employee Information Extractor
Problem Statement
An HR department stores employee IDs, email addresses, and office locations. The team wants to extract department codes, email domains, and city names from the existing records.
Given Data
| Employee Name | Employee ID | Email Address | Office Location |
|---|---|---|---|
| Amit Kumar | EMP-HR-101 | amit@company.com | Dwarka, New Delhi |
| Priya Sharma | EMP-IT-202 | priya@company.com | Sector 15, Gurugram |
| Rahul Verma | EMP-FN-303 | rahul@company.com | Andheri, Mumbai |
| Neha Gupta | EMP-MK-404 | neha@company.com | Salt Lake, Kolkata |
| Rohit Singh | EMP-OP-505 | rohit@company.com | Whitefield, Bengaluru |
Your Task
Add three columns:
- Department Code
- Email Domain
- Office City
Use TEXTAFTER to extract the required information.
Formula for Department Code:
=TEXTAFTER(B2,"-",1)
Formula for Email Domain:
=TEXTAFTER(C2,"@")
Formula for Office City:
=TEXTAFTER(D2,", ")
Copy all three formulas down for the five employees.
Expected Result
| Employee Name | Department Code | Email Domain | Office City |
|---|---|---|---|
| Amit Kumar | HR-101 | company.com | New Delhi |
| Priya Sharma | IT-202 | company.com | Gurugram |
| Rahul Verma | FN-303 | company.com | Mumbai |
| Neha Gupta | MK-404 | company.com | Kolkata |
| Rohit Singh | OP-505 | company.com | Bengaluru |
Key Takeaways
- TEXTAFTER extracts text appearing after a specified delimiter.
- Its syntax is
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]). - The default instance number is 1.
- A negative instance number searches from the end.
- You can extract text after symbols, spaces, commas, and hyphens.
- Match mode
1enables case-insensitive matching. - TEXTAFTER is available in Microsoft 365 and Excel 2024.
- If the delimiter is not found, Excel returns
#N/Aunless an alternative result is provided.
FAQs About the TEXTAFTER Function in Excel
1. What is the TEXTAFTER function in Excel?
TEXTAFTER extracts text that appears after a specified character, word, or delimiter within a text value.
2. What is the syntax of the TEXTAFTER function?
The syntax is =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]).
3. Can TEXTAFTER extract text after the last hyphen?
Yes. Use -1 as the instance number to search for the last occurrence of the delimiter.
4. Can TEXTAFTER In excel extract an email domain?
Yes. Use =TEXTAFTER(A2,"@") to extract everything after the @ symbol.
5. What happens if the delimiter is not found?
Excel returns a #N/A error by default. You can specify an alternative result using the if_not_found argument.
6. Is TEXTAFTER case-sensitive?
By default, TEXTAFTER is case-sensitive. You can use match_mode 1 to perform a case-insensitive search.
7. What is the use of a negative instance number?
A negative instance number tells Excel to search for the delimiter from the end of the text.
8. Which Excel versions support TEXTAFTER?
TEXTAFTER is available in Microsoft 365 and Excel 2024. It is not available in many older Excel versions.

