

Sometimes, you need to change text in a systematic way: If you want to replace text with new text, there are two options. If you only want to do it once, using the Find-and-Replace dialogue is probably the fastest choice. If you want to do it repeatedly or don’t want to mess with your input data, you should try the SUBSTITUTE formula.
Contents
The SUBSTITUTE formula has four arguments.
The SUBSTITUTE formula replaces parts of text. It searches for some text (“string”) within another text and once found replaces it.
The SUBSTITUTE formula has 4 parts:
Let’s use this knowledge and take a look at some examples.
Say you have the text “This cell contains text” in cell B3. You want to search for the text given in cell C3 (“text”) and replace it by the text in cell D4 (“some text”). So, you add the word “some” in front of text.
Example for the SUBSTITUTE formula.
The complete formula looks like this:
=SUBSTITUTE(B3,C3,D3)
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
In a second example, you want to replace only the last occurrence of some text. Again, you can use the SUBSTITUTE formula. But instead of leaving the last argument blank, you must specify that you want to replace the last instance. Therefore, you count the number of instances. This can be done with a trick, also using the SUBSTITUTE formula.
Another example.
The first three arguments are like the previous example:
(LEN(B4)-LEN(SUBSTITUTE(B4,C4,””)))/LEN(C4)
Now you can combine it to the complete formula:
=SUBSTITUTE(B4,C4,D4,(LEN(B4)-LEN(SUBSTITUTE(B4,C4,””)))/LEN(C4))
The difference to the REPLACE formula: The REPLACE formula solves a similar purpose like the SUBSTITUTE formula. But instead of letting Excel search for a certain string, you must provide the start number of characters you want to replace as well as the total number of character to be replaced.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.