How do I return a blank cell instead of an error when the delimiter is missing?

Supply an empty string as the if_not_found argument, for example =TEXTAFTER(A2, ":", 1, 0, 0, ""). This tells TEXTAFTER to output a zero-length string whenever the colon cannot be found.

Can TEXTAFTER work with numbers formatted as dates?

Yes, but the function treats the input as text. Convert the date to a text string first (e.g., using TEXT(date, "yyyy-mm-dd")) or ensure the cell already contains a textual representation.

What is the difference between match_mode = 0 and match_mode = 1?

match_mode = 0 requires an exact match of the delimiter. match_mode = 1 enables wildcard characters (* and ?) inside the delimiter, allowing flexible pattern matching such as "*end" to find any substring that ends with "end".

Why does TEXTAFTER sometimes return an empty string even though the delimiter is present?

An empty result occurs when the delimiter is the last character in the source text or when you request an occurrence that sits at the end of the string. In those cases there are no characters after the delimiter, so TEXTAFTER correctly returns a zero-length string.

TEXTAFTER function

TEXTAFTER returns the substring that appears after a specified delimiter within a text string, optionally selecting which occurrence to use.

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

Generate a TEXTAFTER formula

Describe what you need. The generator will reach for TEXTAFTER where TEXTAFTER is the right tool, and tell you when it is not.

Arguments

How TEXTAFTER reads its arguments

Argument	Required	Description
text	Required	The source string or cell reference containing the text to search; if omitted or an error, TEXTAFTER propagates the error.
delimiter	Required	A required string that marks where the returned text begins; if the delimiter is not found, the function returns #VALUE! unless if_not_found is supplied.
instance_num	Optional	Optional positive integer indicating which occurrence of the delimiter to use; defaults to 1, and values ≤0 trigger #NUM!.
match_mode	Optional	Optional 0 (default) for exact match or 1 for wildcard match; any other value yields #VALUE!.
match_end	Optional	Optional 0 (default) to start after the delimiter or 1 to start after the end of the delimiter; invalid values cause #VALUE!.
if_not_found	Optional	Optional value to return when the delimiter cannot be located; if omitted, the function returns #VALUE!.

Returns

It returns a single text string (a scalar) containing the characters after the chosen delimiter.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Extract the detail part of a task description

Task	Owner	Start Date	Due Date	Hours Logged
Design: UI Mockup	Alice Smith	2023-09-01	2023-09-05	12
Develop: Backend API	Bob Jones	2023-09-03	2023-09-10	20

=TEXTAFTER(A2, ":")

Result: UI Mockup

Column A contains task strings that combine a phase and a description separated by a colon. TEXTAFTER looks for the first ":" in A2 ("Design: UI Mockup") and returns everything after it, which is "UI Mockup". If a row lacked a colon, the function would return #VALUE! unless an if_not_found argument were supplied.

2. Get the last name of each task owner

Task	Owner	Start Date	Due Date	Hours Logged
Design: UI Mockup	Alice Smith	2023-09-01	2023-09-05	12
Develop: Backend API	Bob Jones	2023-09-03	2023-09-10	20
Test: Integration	Charlie Brown	2023-09-07	2023-09-12	8

=TEXTAFTER(B3, " ")

Result: Brown

The Owner column (B) stores full names separated by a space. TEXTAFTER with a space delimiter on B3 ("Charlie Brown") returns everything after the first space, i.e., the last name "Brown". Because instance_num defaults to 1, only the first space is considered; using instance_num = 2 would return an empty string for names with only two parts.

3. Pull the month component from a due-date string

Task	Owner	Start Date	Due Date	Hours Logged
Design: UI Mockup	Alice Smith	2023-09-01	2023-09-05	12
Develop: Backend API	Bob Jones	2023-09-03	2023-10-10	20

=TEXTAFTER(C2, "-", 2, 0, 0, "N/A")

Result: 09

Due Date values are stored as ISO strings (YYYY-MM-DD). By asking TEXTAFTER to find the second occurrence of "-" (instance_num = 2), the function skips the year and returns the month portion. match_mode = 0 (exact) and match_end = 0 (start after the delimiter) give the clean month "09" for the first row. The optional if_not_found argument supplies "N/A" should the date format change.

Common errors

Which TEXTAFTER error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The specified delimiter does not exist in the source text and no if_not_found argument was provided.	Add a fourth argument with a fallback value, e.g., =TEXTAFTER(A2, ":", 1, 0, 0, "No detail")
#NUM!	instance_num is zero or negative, which is outside the allowed range of positive integers.	Supply a positive integer such as 1 or omit the argument entirely to default to the first occurrence.
#REF!	The text argument references a cell that itself contains an error like #DIV/0! or #N/A.	Correct the upstream error or wrap the reference in IFERROR to supply a safe default before calling TEXTAFTER.

Tips and when to use something else

Use TEXTBEFORE when you need the part of a string that appears before a delimiter; TEXTAFTER is the complementary function.
If you only need a fixed number of characters from the start or end, LEFT or RIGHT are faster and avoid delimiter parsing.
Set match_mode to 1 to treat * and ? as wildcards, useful when delimiters vary in length.
When the delimiter may appear multiple times and you need the last segment, combine TEXTAFTER with LEN and SUBSTITUTE to locate the final occurrence.

Related functions

TEXTBEFORETEXTBEFORE returns the substring before a given delimiter, letting you pick which occurrence and supply your own value when the delimiter is absent.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.FINDFIND returns the numeric position of a substring within a text string, counting from a specified start point and respecting case sensitivity.SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

How do I return a blank cell instead of an error when the delimiter is missing?: Supply an empty string as the if_not_found argument, for example =TEXTAFTER(A2, ":", 1, 0, 0, ""). This tells TEXTAFTER to output a zero-length string whenever the colon cannot be found.
Can TEXTAFTER work with numbers formatted as dates?: Yes, but the function treats the input as text. Convert the date to a text string first (e.g., using TEXT(date, "yyyy-mm-dd")) or ensure the cell already contains a textual representation.
What is the difference between match_mode = 0 and match_mode = 1?: match_mode = 0 requires an exact match of the delimiter. match_mode = 1 enables wildcard characters (* and ?) inside the delimiter, allowing flexible pattern matching such as "*end" to find any substring that ends with "end".
Why does TEXTAFTER sometimes return an empty string even though the delimiter is present?: An empty result occurs when the delimiter is the last character in the source text or when you request an occurrence that sits at the end of the string. In those cases there are no characters after the delimiter, so TEXTAFTER correctly returns a zero-length string.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator