TEXTBEFORE
TEXTBEFORE(text, delimiter, [instance])
The part of a piece of text before a delimiter, such as the zone at the start of "Zone 01A - Rebar" or the prefix of a task code. Unlike LEFT, it doesn't need the part to be a fixed width: "Zone 01A" and "Zone 1004" are both cut at the " - ". It works as Excel's does, and finds the delimiter ignoring case and accents, like CONTAINS.
Arguments
| Argument | What to give it |
|---|---|
| text | The text to cut, like [Name], [Task code] or [WBS]. Blank gives blank. |
| delimiter | The text that ends the part you want, like " - " or "-". 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=TEXTBEFORE([WBS], " / ")
The top level of the task's WBS path: "Site" for "Site / Civil". Blank for a task in no node below the top.
Formula=TEXTBEFORE([Task code], "_")
The prefix of the task code, however long: "BDC" for BDC_001, "MS" for MS_SUB, and blank for A1000, which has no underscore.
Formula=COUNT(FILTER(Predecessors, TEXTBEFORE(Predecessors[WBS], " / ") <> TEXTBEFORE([WBS], " / ")))
How many predecessors sit in another top-level area of the WBS.
Formula=TEXTBEFORE("FAB-G30-PR-001", "-", -1)
Everything before the last hyphen: "FAB-G30-PR".
Reach for something else when
| You want | Use |
|---|---|
| The part after the delimiter | TEXTAFTER |
| A fixed number of characters | LEFT |
| A number out of the part | VALUE(TEXTBEFORE([Task code], ".")) |
| Only whether the delimiter is there | CONTAINS |
Common questions
Why is it blank and not the whole text when the delimiter isn't there?
As in Excel: there is nothing "before" a delimiter that isn't there. Write COALESCE(TEXTBEFORE([Name], " - "), [Name]) for the whole text instead.
Why is an empty delimiter refused?
Empty text is found at the start of every text, so the result would always be empty.
The editor refuses this=TEXTBEFORE([Name], "")