how to deal with "~"

[kelly]

Hi, all

i have a problem to lookup or replace words when it contains "~"
character.
every time i have to change it to "-" manualy. for there are lots of
"~" in the workfile, too much time has been wasted on it.
Can anybody have any idea to solve this?

thanks

 

[Rick Rothstein \(MVP - VB\)]

Use two of them. If you are looking for AB~CD, put AB~~CD in the "Find what"
field.

Rick

 

[Dave Peterson]

Just to add...

Excel can use wildcards:
* for any characters
? for any single character
~ as an "escape" character to indicate that the next * or ? should be treated as
an asterisk or question mark--not as wild cards.

So if you're using an =vlookup() for instance, you can avoid wildcard problems
with a formula like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)