SUBSTITUTE
SUBSTITUTE(text, old, new)
Replaces every occurrence of one piece of text with another, such as the separators in a WBS path. Unlike CONTAINS and =, it matches case exactly: replacing "civil" leaves "Civil" as it was.
Arguments
| Argument | What to give it |
|---|---|
| text | The text to change, like [WBS] or [Name]. Blank gives blank. |
| old | The text to replace, with its capitals as they appear. Empty text changes nothing. |
| new | What to put in its place. Use "" to remove old. |
Examples
Formula=SUBSTITUTE([WBS], " / ", " > ")
The task's WBS path with a different separator between the levels.
Formula=SUBSTITUTE([Task code], "_", "-")
The task code with underscores turned into dashes, for matching codes from another system.
Formula=SUBSTITUTE([Name], " ", " ")
The task name with each double space made single.
Reach for something else when
| You want | Use |
|---|---|
| To know whether the text has the part at all | CONTAINS |
| Spaces removed from the ends only | TRIM |
| Part of the text cut out by position | LEFT, MID or RIGHT |
Common questions
Can I replace only the first occurrence?
No. SUBSTITUTE has no fourth argument as Excel's does; it always replaces every occurrence.
Why didn't SUBSTITUTE replace a word I can see?
It matches capitals exactly, unlike the rest of the text functions. Write old with the capitals the text has, or replace each spelling in turn: SUBSTITUTE(SUBSTITUTE([Name], "civil", "CIV"), "Civil", "CIV").