Excel recognize "~"

[TWC]

I am using the "vlookup" function and it does not recognize the "~" tilde
symbol in the data I'm looking up. How do I get Excel to recognize the
symbol?

 

[Bob Phillips]

Escape it with another ~, like

=VLOOKUP("~~",K1:N9,2,FALSE)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[TWC]

The "~" symbol should be proceeded by "~" in the function, for example if I
was looking for "123~" or "123?", etc., I would list this as "123~~" or
"123~?" in my function.

 

[KL]

Excel treats a single ~ as a wildcard character. Use another tilde to make
Excel understand it as a normal character:

=VLOOKUP("~~",A1:B5,2,FALSE)

the same will happen with another wildcard character - * (asterisk)

=VLOOKUP("~*",A1:B5,2,FALSE)

For more info see Help for "wildcard"

Regards,
KL
(XL 97, 2000, 2002)

 

[Dave Peterson]

One way is to have your =vlookup() fix any wild card characters (* and ?) and
the character that's used to indicate that it shouldn't be treated like a wild
card (~):

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

===
And yep, what you wrote is correct.

 

[KL]

I tried it on my spreadsheet and it looks like, for some reason, ? needs no
special treatment in VLOOKUP, but ~ and * do.

KL

--
Saludos,
KL
(XL 97, 2000, 2002)
------------
Ojo - mi separador de argumentos en las formulas es la coma ",".
Puede q necesites cambiarla por punto y coma ";".

Para usar mi direccion de correo electronico privada
borra "NOSPAM" y "PLEASE" antes de usarla.
------------

 

[Dave Peterson]

Try putting this in A1:
asdf?asdf

Then in C1:C2
asdfqasdf
asdf?asdf
and in D12
333
444

Then in B1:
=vlookup(a1,c:d,2,false)

I would expect you to get 333 instead of 444 that I would want.

 

[KL]

Thanks Dave.

KL

Try putting this in A1:
asdf?asdf

Then in C1:C2
asdfqasdf
asdf?asdf
and in D12
333
444

Then in B1:
=vlookup(a1,c:d,2,false)

I would expect you to get 333 instead of 444 that I would want.

 

[TWC]

Thank you everybody. You all were a great help. Before I found this
discussion area, I spent hours looking on the Microsoft website, as well as
on the general internet trying to find an answer. I even tried calling
Microsoft support, who directed me to my computers manufacturer, which was no
help. Thanks again.

 

[Harald Staff]

I even tried calling
Microsoft support, who directed me to my computers manufacturer,

With an Excel formula problem ????????
Just when you thought you heard it all...

Best wishes Harald