How to Replace Text in a Cell with SUBSTITUTE in Excel

Swap words and separators with SUBSTITUTE — and replace just one occurrence — while converting a UK product catalog for a US store.

You will learn to

• Replace every occurrence of a word with SUBSTITUTE
• Swap separator characters in codes
• Replace only the nth occurrence with instance_num

How it works

=SUBSTITUTE(text, old_text, new_text) replaces every occurrence of old_text with new_text: =SUBSTITUTE(A2,"colour","color") converts British spellings in a description. Unlike Find & Replace, the original stays untouched and the formula keeps working when new rows arrive.

SUBSTITUTE is case-sensitive, so "Colour" and "colour" are different matches. A fourth argument, instance_num, replaces only one specific occurrence: =SUBSTITUTE(C2,"-","/",2) changes only the second dash. Replace with "" to delete text, and nest several SUBSTITUTEs to make multiple swaps.

=SUBSTITUTE(A2,"colour","color")