Excel recognize "~"
 

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?

Escape it with another ~, like

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

--

HTH

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


"TWC" wrote in message
...
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?

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.

"TWC" wrote:

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?

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)

"TWC" wrote in message
...
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?

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.

TWC wrote:

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.

"TWC" wrote:

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?

--

Dave Peterson

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" wrote in message
...
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.

TWC wrote:

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.

"TWC" wrote:

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?

--

Dave Peterson

Try putting this in A1:
asdf?asdf

Then in C1:C2
asdfqasdf
asdf?asdf
and in D1:D2
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 wrote:

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" wrote in message
...
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.

TWC wrote:

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.

"TWC" wrote:

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?

--

Dave Peterson

--

Dave Peterson

Thanks Dave.

KL

"Dave Peterson" wrote in message
...
Try putting this in A1:
asdf?asdf

Then in C1:C2
asdfqasdf
asdf?asdf
and in D1:D2
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 wrote:

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" wrote in message
...
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.

TWC wrote:

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.

"TWC" wrote:

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?

--

Dave Peterson

--

Dave Peterson

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.

"TWC" wrote:

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?

"TWC" skrev i melding
...
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