Excel SUBSTITUTE: Replace matching text in a cell

Use Excel’s SUBSTITUTE function to replace a particular word or symbol. Leave out the fourth argument to replace every match, or enter 2 to replace only the second match. The function distinguishes uppercase from lowercase and returns its result separately from the source cell.

Choose the source, text to find, and replacement

=SUBSTITUTE(text,old_text,new_text,[instance_num])

The first argument is the original text or its cell reference. The second is the text to find, and the third is the replacement. The optional fourth argument counts matches from left to right. A value of 2 means the second match, regardless of its character position.

Microsoft’s SUBSTITUTE syntax and argument reference

Compare replacing every match with replacing only the second

Enter AB-CD-cd-CD in A2 and try each formula in a separate blank cell. The uppercase text CD occurs twice. The lowercase cd in the middle does not match.

Formula Result What changes
=SUBSTITUTE(A2,"CD","ZZ") AB-ZZ-cd-ZZ Replace both uppercase CD matches
=SUBSTITUTE(A2,"CD","ZZ",2) AB-CD-cd-ZZ Replace only the second uppercase CD
=SUBSTITUTE(A2,"CD","ZZ",3) AB-CD-cd-CD Keep the original because there is no third match

Remove hyphens and spaces while keeping leading zeros

Enter 00-27 04 as text in A6. If Excel converts the input, enter it with a leading apostrophe. The first formula removes every hyphen and returns 0027 04. The second also removes ordinary spaces and returns 002704.

=SUBSTITUTE(A6,"-","")
=SUBSTITUTE(SUBSTITUTE(A6,"-","")," ","")

Nesting feeds the result of the inner SUBSTITUTE to the outer SUBSTITUTE, so you can remove different characters in order. Keep identifiers as text if leading zeros matter. These formulas cannot restore zeros that were lost on entry.

Microsoft’s guide to keeping leading zeros as text

Troubleshoot a replacement that did not occur

Check the case, the type of dash, and the type of space in the source. Searching for an ordinary space leaves nonbreaking spaces in place. Use TRIM when you want to remove leading and trailing ordinary spaces and keep one space between words.

Use REPLACE when the position is fixed even if the text varies. For example, you can change two characters starting at position 4 in every code by specifying the position and length.