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:
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.
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