CONTAINS
CONTAINS(text, part)
TRUE when a piece of text has another inside it, such as a task name that mentions "pour". It ignores case and accents, so "Pour", "POUR" and "pour" all match, "becancour" finds "Bécancour", and Arabic matches without its harakat. Otherwise it looks for the part exactly as written, with no wildcards.
Arguments
| Argument | What to give it |
|---|---|
| text | The text to look in, like [Name], [WBS] or an attribute. A choice field such as [Status] works too. Blank gives blank, not FALSE. |
| part | The text to look for. Empty text is found in everything. |
Examples
Formula=CONTAINS([Name], "pour")
TRUE for tasks whose name mentions a pour, in any capitals.
Formula=COUNT(FILTER(Predecessors, CONTAINS(Predecessors[Name], "inspection")))
How many of the task's predecessors are inspections.
Formula=CONTAINS([Name], "pour") AND [Total float] < 5d
As a filter: pours with less than five working days of float.
Formula on WBS rows=COUNT(FILTER(Tasks, CONTAINS(Tasks[Name], "submittal")))
On a WBS row: how many tasks in the node and below it are submittals.
Reach for something else when
| You want | Use |
|---|---|
| Only text that begins with the part | STARTSWITH |
| Text that ends with the part | RIGHT: RIGHT([Task code], 3) = "SUB" |
| The text with the part replaced | SUBSTITUTE |
Common questions
Is there a SEARCH or FIND?
No. Excel's ISNUMBER(SEARCH("pour", A2)) is CONTAINS([Name], "pour"). There's no function that gives the position of the part; to cut the text at it, use TEXTBEFORE or TEXTAFTER.
Why is the result blank on some rows, not FALSE?
A blank field is never empty text, so CONTAINS on it is blank too, and a filter or guardrail skips that row. Write CONTAINS(COALESCE([Phase], ""), "civ") to read a blank as no match. See blanks and errors.