Replacing Text With SUBSTITUTE

Data often needs find-and-replace inside formulas: stripping dashes from phone numbers, swapping a word, or removing stray symbols.

The SUBSTITUTE function finds a piece of text inside a cell and replaces every occurrence with new text, all without changing the original cell.

It is the formula-based equivalent of Find & Replace, but it updates automatically as your data changes.

SUBSTITUTE takes up to four arguments:

• text the original string or cell
• old_text the text to find
• new_text the text to put in its place
• instance_num (optional) which occurrence to replace

The shape is =SUBSTITUTE(text, old_text, new_text, [instance_num]). If you omit the last argument, all occurrences are replaced.

A common trick is replacing something with nothing to delete it. You do this by setting new_text to an empty string "".

If A2 holds 555-123-4567, the formula below removes every dash, returning 5551234567.

This pattern cleans phone numbers, IDs, and other punctuated values.

SUBSTITUTE works on whole words too, not just single characters.

If A2 holds Annual report 2023, you can update the year by replacing the word:

The result is Annual report 2024. Every matching occurrence in the cell is changed.

Important: SUBSTITUTE matches text exactly, including capitalization.

If A2 holds Cat cat CAT, then =SUBSTITUTE(A2, "cat", "dog") only changes the lowercase cat, giving Cat dog CAT.

If you need case-insensitive replacing, first normalize the case with LOWER or UPPER, or use the next lesson's regex functions in Sheets.

The optional fourth argument, instance_num, lets you target a single occurrence instead of all of them.

If A2 holds a-b-c-d and you only want to change the second dash, set instance_num to 2.

The result is a-b/c-d: only the second dash became a slash.

Imagine an imported amount stored as text: $1,250.00. To turn it into a usable number you must strip the dollar sign and the comma.

Nest two SUBSTITUTE calls, one inside the other, to remove both characters.

The inner call removes the comma; the outer call removes the dollar sign, leaving 1250.00.

You can nest as many SUBSTITUTE calls as you need, each cleaning a different character.

To normalize a messy code that contains spaces, dots, and slashes into clean dashes, chain three substitutions.

Each layer feeds its result into the next, building up the final cleaned string.

Do not confuse SUBSTITUTE with the similar REPLACE function.

• SUBSTITUTE replaces text by matching its content (find this word, swap it).
• REPLACE replaces text by position (replace 4 characters starting at position 5).

Use SUBSTITUTE when you know what to find, and REPLACE when you know where it sits.

A clever bonus: combine SUBSTITUTE with LEN to count how many times something appears.

Subtract the length after removing a character from the original length. If A2 holds a,b,c,d, this counts the commas, returning 3.

This trick is great for counting items in a delimited list.

Suppose A2 holds messy numbers with dots and spaces like 555.123 4567. You want a consistent dashed format 555-123-4567.

First strip the dots and spaces, then you could re-insert dashes; for now, simply unify the separators to dashes.

Nesting substitutions standardizes whatever junk separators appear.

Test your understanding of SUBSTITUTE.

You learned to find and replace text in formulas:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Use "" as new_text to delete characters.
• It is case-sensitive; normalize case first if needed.
• The optional instance_num targets a single occurrence.
• Nest calls to clean multiple characters; pair with LEN to count occurrences.

You have completed Joining and Changing Text. Great work building clean, consistent data!