(substitute, replace) VS replace tool

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,559
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,448
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Mar 27, 2016
Messages
1,559
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,448
Office Version
2013
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,719
Office Version
365
Platform
Windows
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.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,448
Office Version
2013
Platform
Windows
Good points Peter.
Also, I guess FIND Replace will do an entire range / col / sheet....insitu, rather than requiring the "helper" cells
 

Watch MrExcel Video

Forum statistics

Threads
1,090,452
Messages
5,414,611
Members
403,536
Latest member
JEduardo

This Week's Hot Topics

Top