Cleaning imported data
SUBSTITUTE vs REPLACE: swap matching text or overwrite by position
Use SUBSTITUTE when you know which text to swap (all occurrences or a chosen Nth match), and REPLACE when you know the character start index and length.
Three text-cleaning tasks with different rules
Row 2 converts slash dates ("2026/03/15") to hyphens, row 3 changes only the second hyphen in "WH-NORTH-04" to a colon, and row 4 masks the middle four digits of "CARD-7781-9024" regardless of what digits appear there.
| Input (A) | Goal | Best function |
|---|---|---|
| 2026/03/15 | 2026-03-15 | SUBSTITUTE (all) |
| WH-NORTH-04 | WH-NORTH:04 | SUBSTITUTE (2nd match) |
| CARD-7781-9024 | CARD-XXXX-9024 | REPLACE (start 6, len 4) |
Swap every match or target instance_num with SUBSTITUTE
=SUBSTITUTE(A2, "/", "-") replaces every forward slash. Supplying 2 as the optional fourth argument—=SUBSTITUTE(A3, "-", ":", 2)—changes only the second hyphen while leaving the first hyphen untouched.
Remember that SUBSTITUTE is case-sensitive: searching for "wh" in "WH-NORTH-04" leaves the text unchanged.
=SUBSTITUTE(A2,"/","-")Overwrite a fixed character slice with REPLACE
When the characters themselves vary on every row, count positions instead: =REPLACE(A4, 6, 4, "XXXX") starts at character 6 and replaces 4 characters with XXXX.
=REPLACE(A4,6,4,"XXXX")Try the example
Use the original values or change them to test your understanding.