Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

SUBSTITUTE function vs REPLACE function in Excel

This example shows the difference between the SUBSTITUTE function and the REPLACE function.

1a. If you know the text to be replaced, use the SUBSTITUTE function.

Worked Example:   Get workbook name and path without sheet in Excel

1b. The SUBSTITUTE function has a 4th optional argument. You can use this argument to indicate which occurrence you want to substitute.

Worked Example:   How to find nth occurrence of character in Excel

2. If you know the position of the text to be replaced, use the REPLACE function.

Worked Example:   Extract last name from full name -- Manipulating NAMES in Excel

Note: started at position 1 with length 3.

Leave a Reply

Your email address will not be published. Required fields are marked *