How to replace ~ with other value ?

  • Thread starter Thread starter moonhkt
  • Start date Start date
M

moonhkt

Hi Reader

I have some cell have ~ in order number column, How to replace ~ to
two -- ?
Due vlookup can not handle ~.

Moonhkt
 
* and ? are wildcards for lots of excel functions (as well as the Edit|Find's).

The ~ character is the escape character that tells excel to not use them as
wildcards.

~* and ~?

And you use ~~ to tell excel not to use ~ as that escape character.

So if your data has the possibility of having *, ? or ~ in and each should not
be treated like wildcards/escape characters, then you could modify your
=vlookup() formula to something like:

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

If you don't care about the * and ? wildcards, you could use:

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