Easy Excel Formula to Extract Text After a Character

Sharing is caring!

Excel’s powerful text functions make it easy to extract specific parts of your data. When you need to extract text after a particular character—for example, everything after an underscore, hyphen, or comma—Excel provides various formulas to help achieve this.

In this article, we’ll guide you through using Excel formulas like TEXTAFTER, RIGHT, SEARCH, and LEN to capture text after a specified character. By the end, you’ll know how to use these functions efficiently for your data extraction tasks.

Using the TEXTAFTER Function to Grab Text After a Character

The TEXTAFTER function in Excel is an efficient way to extract text that occurs after a specified character or string within a cell. It is often used when you need to isolate specific data segments in text strings, like domain names in emails, suffixes in codes, or portions of phrases. 

TEXTAFTER is essentially the reverse of the TEXTBEFORE function, which retrieves text preceding a specified delimiter.

Syntax of the TEXTAFTER Function

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Arguments in TEXTAFTER

• text: The cell or text string in which you want to search. Wildcard characters are not allowed.
• delimiter: The specific character or string after which you want to extract text. For example, @, -, or a space.
• instance_num (optional): Specifies which instance of the delimiter to use. By default, this is set to 1 (the first instance). Setting a negative number starts searching from the end of the text.
  • A positive instance_num searches from the start.
  • A negative instance_num searches from the end.
• match_mode (optional): Determines whether the function is case-sensitive. Default is case-sensitive.
  • 0: Case-sensitive
  • 1: Case-insensitive
• match_end (optional): Specifies if the end of the text should be treated as a delimiter. By default, the text is an exact match.
  • 0: Do not treat the end as a delimiter.
  • 1: Treat the end as a delimiter.
• if_not_found (optional): Specifies the value to return if the delimiter is not found in the text. By default, this returns #N/A.

Key Points to Remember

• If delimiter is empty, TEXTAFTER matches immediately. It will return the entire text when searching from the start (if instance_num is positive) and empty text when searching from the end (if instance_num is negative).
• TEXTAFTER will return #N/A if the specified delimiter is not in the text.
• Setting instance_num to 0 or a number exceeding the occurrences in the text will return #VALUE! or #N/A.

Examples of TEXTAFTER Function

Let’s look at a few practical examples to better understand how TEXTAFTER works.

Example 1: Basic Usage of TEXTAFTER

Suppose you have the phrase "Catherine-Baker-2023" in cell A1, and you want to extract everything after the first hyphen (-).

=TEXTAFTER(A1, "-")

Result: "Baker-2023" (text after the first hyphen).

Example 2: Specifying an Instance Number

If you need text after the second occurrence of the hyphen, set instance_num to 2.

=TEXTAFTER(A1, "-", 2)

Result: "2023" (text after the second hyphen).

Example 3: Using Case-Insensitive Match Mode

Consider the text "Project-Q4-2023" in cell A2. If you want to extract text after project (case-insensitive), use match_mode = 1.

=TEXTAFTER(A2, "project", 1, 1)

Result: "Q4-2023" (matches project in any case).

Example 4: Handling No Match with if_not_found

If the delimiter doesn’t exist in the text, use if_not_found to prevent Excel from returning an error.

=TEXTAFTER(A2, "XYZ", , , , "Not found")

Result: "Not found" (since "XYZ" does not appear in the text).

Example 5: Treating the End of Text as a Delimiter

When extracting the last part of a name in cell B2 (e.g., "Emily White"), you can treat the end of text as a delimiter.

=TEXTAFTER(B2, " ", , , 1)

Result: "White" (since it treats the end of the text as a delimiter after "White").

Example 6: Extracting Subdomain from a URL

Assume you have a URL in cell C2: "www.example-subdomain.com", and you want to capture the text after "example-".

=TEXTAFTER(C2, "example-")

Result: "subdomain.com" (text after "example-").

Advanced Applications of TEXTAFTER Function

Here are some creative ways to use TEXTAFTER for data extraction:

1. Extracting Extensions from Filenames: In a cell containing "report_final.docx", use =TEXTAFTER(A3, ".") to get the file extension ("docx").
2. Isolating Last Names: For a name in "John A. Doe", use =TEXTAFTER(A4, " ", -1) to extract the last name ("Doe").
3. Capturing Hostnames from Emails: If D2 contains "[email protected]", =TEXTAFTER(D2, "@") will return "example.com".

Example Use Cases of TEXTAFTER Function

Using RIGHT, LEN, and SEARCH Functions to Extract Text After a Character

If you’re using an older version of Excel without the TEXTAFTER function, you can use a combination of RIGHT, LEN, and SEARCH functions to extract text after a character

Syntax:

1. RIGHT: Extracts text from the end of a string.
2. LEN: Returns the total character length of a text string.
3. SEARCH: Finds the position of a specific character or substring.

Let’s go through an example:

Example:

Suppose cell A2 contains the text ABC_12345, and you want to extract the numeric part after the underscore _.

=RIGHT(A2, LEN(A2) - SEARCH("_", A2))

Explanation:

• SEARCH(“_”, A2) finds the position of the underscore in cell A2.
• LEN(A2) gives the total length of the text.
• RIGHT(A2, LEN(A2) – SEARCH(“_”, A2)) extracts all characters from the underscore to the end.

This formula results in “12345”, providing the text after the underscore.

Using FIND to Extract Text with Multiple Instances of a Character

If a text string has multiple delimiters, like “Order-2023-Q3-Sales” in cell B1, and you want text after the second hyphen, you can use nested FIND functions.

=MID(B1, FIND("-", B1, FIND("-", B1) + 1) + 1, LEN(B1))

Explanation:

1. FIND(“-“, B1) identifies the first hyphen’s position.
2. FIND(“-“, B1, FIND(“-“, B1) + 1) finds the second hyphen.
3. MID(B1, FIND(“-“, B1, FIND(“-“, B1) + 1) + 1, LEN(B1)) extracts text starting after the second hyphen.

The output is “Sales”.

Using SUBSTITUTE for Custom Delimiters

For cases where your text has custom characters, such as multiple hyphens or underscores in different parts, SUBSTITUTE is a useful function to replace characters and simplify extraction.

Suppose you have Invoice-2023-Q3-Sales in cell D1, and you want only the Sales part. Using SUBSTITUTE with RIGHT can help:

=RIGHT(D1, LEN(D1) - FIND("@", SUBSTITUTE(D1, "-", "@", LEN(D1) - LEN(SUBSTITUTE(D1, "-", "")))))

Putting It All Together in a Table

Here’s a summary table of key methods to grab text after a character:

Practical Applications of Extracting Text After a Character

Knowing how to extract text after a specific character is essential in several fields:

1. Database Management: Organize and separate different parts of product codes, IDs, or SKUs.
2. Email Marketing: Quickly extract email domains for analysis.
3. Finance and Sales: Extract data from structured codes, e.g., extracting order numbers or categories.

Final Thoughts

Extracting text after a character in Excel is a useful skill that saves time and boosts productivity. With options ranging from the powerful TEXTAFTER function to combinations of RIGHT, LEN, and SEARCH, you have multiple ways to handle this task. Whether you’re working with email addresses, product codes, or financial data, these formulas make data management easier and more efficient.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.