The SUBSTITUTE function
Replaces existing text with new text, everywhere or only the n-th time it appears.
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])- text
- The text to change.
- old_text
- The text to replace.
- new_text
- The replacement. "" removes old_text.
- instance_num (optional)
- Which occurrence to replace. Left out, every one is replaced.
Examples
C1
=SUBSTITUTE(1A2,2"-",3"")| A | B | C | |
|---|---|---|---|
| 1 | Code | DK2100 | |
| 2 | DK-2100 |
In plain words: In 1the code in A2, replace 2every dash with 3nothing.
C1=SUBSTITUTE("a-b-c","-","/",2)
| A | B | C | |
|---|---|---|---|
| 1 | Code | a-b/c | |
| 2 | DK-2100 |