How to use SUBSTITUTE function in excel with multiple text replacement

Excel version in which SUBSTITUTE function can work:

SUBSTITUTE function will work in Excel for Office 365 Excel for Office 365 for Mac Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Excel Mobile Excel Starter 2010.

About SUBSTITUTE function in excel:

SUBSTITUTE function is a worksheet text function, this function substitutes or replaces the new text in the place of old text in a text string. This replaces the specific text in any place in a text string.

SUBSTITUTE is case-sensitive and does not support wildcards.
We can use nested SUBSTITUTE to replace multiple texts.

Syntax for SUBSTITUTE Function:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Parameters:

The SUBSTITUTE function syntax has the following parameters:

Text [Required]: The text or the reference to a cell containing text for which you want to substitute characters.
Old_text [Required]: The text you want to replace.
New_text [Required]: The text you want to replace old_text with.
Instance_num [Optional]: Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Using SUBSTITUTE function:

This function can be used replace specific text in any place in string or to remove certaing text in string etc. By adding instance number, we can specify which text to replace instead of all matching texts.

Text String    Result Formula Comment
Munday Monday  =SUBSTITUTE(A2,”u”,”o”) This will replace u with o
9855-4344-1211-4545 9855434412114545  =SUBSTITUTE(A3,”-“,””) This will remove the – between numbers
extra effort Extra effort  =SUBSTITUTE(A4,”e”,”E”,1) This will replace first instance of e with E.
P1, P2, P3 Project One, Project Two, Project Three  =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,”P1″,”Project One”),”P2″,”Project Two”),”P3″,”Project Three”) We can use the nesed SUBTITLE to

SUBSTITUTE

1 thought on “How to use SUBSTITUTE function in excel with multiple text replacement”

  1. Hello, I was wondering if you knew how to replace multiple items at the same time, so basically I need to turn every current “a” into “b” and every current “b” into “a” while also turning current “A” into “G” and every current “G” into “F” if that makes sense. I don’t know if that’s possible in excel or if you know a program i can do this in. Many thanks for any suggestions and help you can provide.

    Reply

Leave a Comment