Replace function --> beautiful

[ErikJan]

Not sure if I'm missing something but I've used the REPLACE function for a while now but every time I realize how nice this one is. I want to start using it (much) more but not sure if I'm missing something...

Replace works with different length strings, so...

String is: "This is a test"

Then REPLACE(String, "is", "was") becomes "This was a test".

If the string is not found, nothing is changed. If the replace-string is empty it works too (and removes the string found).

Then there's a number of replacements one can specify...

I like it! Just making readers who haven't seen or used this little diamond aware.

[HansV]

Also take a look at the optional last argument, Compare. By default, Replace is case sensitive:

MsgBox Replace(Expression:="Boys will be boys", Find:="boys", Replace:="girls")

will display "Boys will be girls". But if you either add a line

Option Compare Text

at the top of the module, or change the code to

MsgBox Replace(Expression:="Boys will be boys", Find:="boys", Replace:="girls", Compare:=vbTextCompare)

the Replace function will be case insensitive, so the result will be "girls will be girls".

[ErikJan]

"Girls would be girls"; where caps would be maintained would be even nicer (but we can't win'em all :-))

[StuartR]

...String is: "This is a test"

Then REPLACE(String, "is", "was") becomes "This was a test"....

Actually this would become "Thwas was a test", which should serve as a warning to people who use the REPLACE function carelessly.

[ErikJan]

Ha... you got me there! Should check my own examples better... thanks for seeing this.