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.
Replace function --> beautiful
-
- BronzeLounger
- Posts: 1254
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Replace function --> beautiful
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".
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".
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1254
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Replace function --> beautiful
"Girls would be girls"; where caps would be maintained would be even nicer (but we can't win'em all :-))
-
- Administrator
- Posts: 12615
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Replace function --> beautiful
Actually this would become "Thwas was a test", which should serve as a warning to people who use the REPLACE function carelessly.ErikJan wrote:...String is: "This is a test"
Then REPLACE(String, "is", "was") becomes "This was a test"....
StuartR
-
- BronzeLounger
- Posts: 1254
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Replace function --> beautiful
Ha... you got me there! Should check my own examples better... thanks for seeing this.