TEXTAFTER Function in Excel Tutorial

Excel Tutorial

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 0 for case-sensitive matching and 1 for case-insensitive matching. The default is 0.
  • match_end: Optional. Use 0 for normal matching and 1 to 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/A error 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 IDDepartment Code
EMP-HR-105HR-105
EMP-IT-208IT-208
EMP-FN-310FN-310
EMP-MK-405MK-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 AddressDomain
amit@gmail.comgmail.com
priya@yahoo.comyahoo.com
rahul@company.incompany.in
neha@vsit.invsit.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 CodeProduct Number
LAP-105105
KEY-205205
MON-310310
USB-450450

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 IDEmployee Number
EMP-HR-10451045
EMP-IT-20862086
EMP-FN-30123012
EMP-MK-40504050

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 AddressCity
Dwarka, New DelhiNew Delhi
Sector 15, GurugramGurugram
Andheri, MumbaiMumbai
Salt Lake, KolkataKolkata

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 CodeExtracted Text
LAP-IT-105105
KEY-HW-205205
MON-DP-310310
USB-AC-450450

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 RecordSearch DelimiterExtracted Text
EMP-HR-105hr-105
EMP-hr-205HR-205
EMP-Hr-310HR-310
EMP-hR-405hr-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 AddressExtracted 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 IDExtracted 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 AddressExtracted 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 NameEmployee IDEmail AddressOffice Location
Amit KumarEMP-HR-101amit@company.comDwarka, New Delhi
Priya SharmaEMP-IT-202priya@company.comSector 15, Gurugram
Rahul VermaEMP-FN-303rahul@company.comAndheri, Mumbai
Neha GuptaEMP-MK-404neha@company.comSalt Lake, Kolkata
Rohit SinghEMP-OP-505rohit@company.comWhitefield, 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 NameDepartment CodeEmail DomainOffice City
Amit KumarHR-101company.comNew Delhi
Priya SharmaIT-202company.comGurugram
Rahul VermaFN-303company.comMumbai
Neha GuptaMK-404company.comKolkata
Rohit SinghOP-505company.comBengaluru

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 1 enables case-insensitive matching.
  • TEXTAFTER is available in Microsoft 365 and Excel 2024.
  • If the delimiter is not found, Excel returns #N/A unless 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.