Excel Function: SUBSTITUTE

The Excel function SUBSTITUTE replaces textual values with others within a string.


=SUBSTITUTE(text, old_value, new_value)


=SUBSTITUTE(text, old_value, new_value, instance_num)

Example of use

The goal here is to remove the characters " / " within the cells of the first column and replace them with a single space " ".

To do this, enter in cell B2:

=SUBSTITUTE(A2," / "," ")
excel function substitute

Then drag down:

excel function substitute replacement characters

You will notice that all the " / " have been replaced.

To replace only the first " / ", enter the position number (enter 1 to replace the first occurrence, 2 for the second, etc):

=SUBSTITUTE(A2," / "," ",1)
excel function substitute replacement first occurrence

This time, only the first " / " has been replaced.

If needed, you can download the Excel file used here: substitute.xlsx