Hi Reader

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

Moonhkt

Double them up, that is use ~~

--

HTH

Bob

"moonhkt" <(E-Mail Removed)> wrote in message
news:c79e71a9-95f0-4f39-aa68-(E-Mail Removed)...
> 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,FA LSE)



moonhkt wrote:
>
> Hi Reader
>
> I have some cell have ~ in order number column, How to replace ~ to
> two -- ?
> Due vlookup can not handle ~.
>
> Moonhkt

--

Dave Peterson