SUBSTITUTE vs REPLACE in Excel

 

If you know the text to be replaced, use the SUBSTITUTE function in Excel. If you know the position of the text to be replaced, use the REPLACE function.

1. The SUBSTITUTE function below replaces 2013 with 2016.

SUBSTITUTE function in Excel

2. The 4th argument (optional) specifies which occurrence you want to replace. The SUBSTITUTE function below only replaces the second occurrence.

Substitute Second Occurrence

3. The SUBSTITUTE function below substitutes spaces with empty strings. In other words, this formula removes all spaces.

Remove Spaces

4. The REPLACE function below starts at position 4 and replaces 2 characters with -P.

REPLACE function in Excel

5. The REPLACE function below starts at position 1 and replaces 3 characters with an empty string. In other words, this formula removes the first 3 characters.

Remove first 3 characters

You can also use Excel's Find and Replace feature to quickly find specific text and replace it with other text.

6. For example, select the range A1:A10.

Select Range

7. On the Home tab, in the Editing group, click Find & Select.

Click Find & Select

8. Click Replace.

Click Replace

9. Type the text you want to find (TX) and the text you want to replace it with (CA).

10. Click 'Replace All'.

Replace All

Result: Excel replaces all occurrences of TX with CA.

4 replacements