MS Excel: SUBSTITUTE() Function

Microsoft’s Excel has a function called SUBSTITUTE(), this function can be used to completely find and replace a substring within a string.

Understanding the way Excel uses SUBSTITUTE()

This formula required 4 different inputs; HAYSTACK, NEEDLE,  NEW NEEDLE, APPEARANCE.

The formula is as follows:

SUBSTITUTE(HAYSTACK,NEEDLE,NEW NEEDLE,APPEARANCE)

Using SUBSTITUTE()

If I had the string “Ian Blott, Web Developer, Sydney Australia” in cell A1 and I to moved to Seattle USA, I can use the SUBSTITUTE() formula to do this for me

SUBSTITUTE(A1,"Sydney Australia", "Seattle USA", 1)

This would return the value “Ian Blott, Web Developer, Seattle USA”.

The attribute APPEARANCE is a very powerful one that MS have included. With this I can deligate that the SUBSITUTE() will either work off the first instance of the NEEDLE, Second or even 30th by simply placing the desired number in the formula.

Side note:

As with all my posts, quite often they are going somewhere and require 2,3 or even 4 different functions to perform the final task. Bellow are the related posts that tell an overall story

Leave a Reply