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.

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

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

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

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.

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.

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

8. 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'.

Result: Excel replaces all occurrences of TX with CA.