(substitute, replace) VS replace tool

[lezawang]

Hi
I know how and when to use substitute() and replace() function. But my question, I found that Replace tool is doing the same. Is there any case that Replace tool is not going to help then I will have to use substitute() or replace() functions. Thank you very much.

 

[Michael M]

Here is some comparisons between the 2 functions, found at this site

https://www.myonlinetraininghub.com...rence-between-substitute-and-replace-function

There are subtle differences and in some cases you can get the same result using both SUBSTITUTE and REPLACE.

SUBSTITUTE allows you to replace 1 or all occurrences of a string in another string. If we have the string "brown fox" in A1 then

=SUBSTITUTE(A1,"o","a")

gives us "brawn fax"

If we specify that we only want to replace the 2nd "o"

=SUBSTITUTE(A1,"o","a",2)

we get "brown fax"



With REPLACE we are replacing a specific part of the string with another and we need to specify from where in the string to start looking for the occurrence of the string we want to replace.

REPLACE requires us to know the position of the substring and its length, SUBSTITUTE is more flexible.

With our string "brown fox" if we want to replace "fox" with "dog" we need to know where in the string "fox" occurs

=SEARCH("fox",A1)

NOTE : You could also use FIND which is case sensitive. SEARCH is case insensitive.

So to REPLACE "fox" with "dog"

=REPLACE(A5,SEARCH("fox",A5),3,"dog")



REPLACE allows you to replace a substring with a another of different length too. To change "brown fox" to "brown bear"

=REPLACE(A5,SEARCH("fox",A5),3,"bear")

 

[lezawang]

Thank you. Sorry I was not clear. I meant what is the difference between using a function like substitute() to replace a text verse using Home ---> Find and Select -->Replace tool ?

For example, I want to replace street with St

A1 = 123 main street

So I can use B1=substitute(A1,"street","st")

or i can select cell and go to Home-->Find and Select-->Replace and type Street in the first box and in the second box I type St

Thank you

 

[Michael M]

Nothing really, except that if you put new data in col "A", Col "B" will be automatically updated, as opposed to having to do a Find / Replace, everytime new data is inserted in Col "A".
Imagine importing data from another source and having to do Find / Replace each time ??

 

[Peter_SSs]

There are some slight differences including ..

If A1 contained "123 main street, streettown"
then Find/Replace would produce the unwanted result "123 main st, sttown"
whereas with SUBSTITUTE you could use the 4th argument to prevent the second substitution

Another difference is that SUBSTITUTE is case-sensitive while Find/Replace has an option about that.
So if column A contained
123 main street
3 SmItH STREET & 6 JONES street
45 Hall Street
then Find/Replace could replace them all with St while maintaining the original case of the rest of the text whereas SUBSTITUTE could not

With Find/Replace you can use wildcards, with SUBSTITUTE you cannot.

 

[Michael M]

Good points Peter.
Also, I guess FIND Replace will do an entire range / col / sheet....insitu, rather than requiring the "helper" cells