TEXTAFTER
TEXTAFTER(text, delimiter, [instance])
The part of a piece of text after a delimiter, such as the structure key at the end of a task code like FAB-G30-PR-001, or the lowest level of a WBS path. It works as Excel's does, and finds the delimiter ignoring case and accents, like CONTAINS; the text it gives back keeps its own capitals and accents.
Arguments
| Argument | What to give it |
|---|---|
| text | The text to cut, like [Task code], [Name] or [WBS]. Blank gives blank. |
| delimiter | The text that comes just before the part you want, like "-" or "Level ". It can't be empty. |
| instance | Which delimiter to cut at: 1 (the first) when you leave it out, 2 the second, and so on. A negative number counts from the end: -1 is the last. 0 is refused. |
When the text doesn't have the delimiter, or not that many times, the result is blank (not an error), so a filter or guardrail skips that row.
Examples
Formula=TEXTAFTER([WBS], " / ", -1)
The lowest level of the task's WBS path: "Civil" for "Site / Civil".
Formula=TEXTAFTER([Task code], "_")
What follows the first underscore of the code: "001" for BDC_001, "SUB" for MS_SUB.
Formula=VALUE(TEXTAFTER("Level 07", "level "))
The floor number in "Level 07": 7. The delimiter matches "Level " whatever its capitals.
Formula=TEXTAFTER("FAB-G30-PR-001", "-")
Everything after the first hyphen: "G30-PR-001".
Reach for something else when
| You want | Use |
|---|---|
| The part before the delimiter | TEXTBEFORE |
| A fixed number of characters from the end | RIGHT |
| The part as a number | VALUE |
Common questions
How do I take the middle part, between two delimiters?
Cut twice: TEXTBEFORE(TEXTAFTER([Task code], "-"), "-") is "G30" in FAB-G30-PR-001.
Is there a FIND or SEARCH?
No function gives the position of a part. TEXTBEFORE and TEXTAFTER cut at the part instead, which is what a position was usually wanted for.