vlookup & "~" symbol
 

Hi

I think i must have missed something in my Excel learning...

What's the deal with the "~" symbol?

if you try to look something up & the text has one of those in it, it messes
up the vlookup.

is this a pretty standard thing that has just passed me by all these years?
is there anyway round it?

cheers

jb

vlookup & "~" symbol
 

Hi

The tilde ~ is used as a wild card.
You could use something like
=VLOOKUP(SUBSTITUTE(A1,"~","^^"),SUBSTITUTE(J1:K10 0,"~","^^"),2,0)

where you are substituting the tilde with something that is unlikely to
be found within the text entries of column J.

--
Regards

Roger Govier


"JB2010" wrote in message
...
Hi

I think i must have missed something in my Excel learning...

What's the deal with the "~" symbol?

if you try to look something up & the text has one of those in it, it
messes
up the vlookup.

is this a pretty standard thing that has just passed me by all these
years?
is there anyway round it?

cheers

jb

vlookup & "~" symbol
 

Excel supports wild cards (* and ?, any set of characters and any single
character).

The tilde is used to tell excel that you don't mean the wildcard--you actually
mean that character ~* and ~?.
Since tilde has a special purpose, you have to treat it special too: ~~.

And if your data has asterisks and question marks, you may want something like:

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

JB2010 wrote:

Hi

I think i must have missed something in my Excel learning...

What's the deal with the "~" symbol?

if you try to look something up & the text has one of those in it, it messes
up the vlookup.

is this a pretty standard thing that has just passed me by all these years?
is there anyway round it?

cheers

jb

--

Dave Peterson

vlookup & "~" symbol
 

Hi Roger


Many thanks for that, did not know that it was used as a wild card, so I
have learned something new today!

I can see the logic of your suggested solution, but I can only get it to
bring back a #VALUE error, could this be because we are trying to amend the
text in a range as well as a single cell?

cheers

jb

"Roger Govier" wrote:

Hi

The tilde ~ is used as a wild card.
You could use something like
=VLOOKUP(SUBSTITUTE(A1,"~","^^"),SUBSTITUTE(J1:K10 0,"~","^^"),2,0)

where you are substituting the tilde with something that is unlikely to
be found within the text entries of column J.

--
Regards

Roger Govier


"JB2010" wrote in message
...
Hi

I think i must have missed something in my Excel learning...

What's the deal with the "~" symbol?

if you try to look something up & the text has one of those in it, it
messes
up the vlookup.

is this a pretty standard thing that has just passed me by all these
years?
is there anyway round it?

cheers

jb

vlookup & "~" symbol
 

Dave


that works a treat. thanks for explaining

cheers

jb

"Dave Peterson" wrote:

Excel supports wild cards (* and ?, any set of characters and any single
character).

The tilde is used to tell excel that you don't mean the wildcard--you actually
mean that character ~* and ~?.
Since tilde has a special purpose, you have to treat it special too: ~~.

And if your data has asterisks and question marks, you may want something like:

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

JB2010 wrote:

Hi

I think i must have missed something in my Excel learning...

What's the deal with the "~" symbol?

if you try to look something up & the text has one of those in it, it messes
up the vlookup.

is this a pretty standard thing that has just passed me by all these years?
is there anyway round it?

cheers

jb

--

Dave Peterson

vlookup & "~" symbol
 

Hi

Dave gave you the correct description about the tilde, it is used to
tell excel to disregard the wildcards * and ?, not to be used as a
wildcard itself.

As for the formula, it worked fine for me when I tested it.
I had Sales~Dept in A1,
and in J1 Accounts~Dept, J2 Accounts Dept, J3 Sales~Dept, J4 Sales Dept
It returned the value of 30 from cell K3

--
Regards

Roger Govier


"JB2010" wrote in message
...
Hi Roger


Many thanks for that, did not know that it was used as a wild card, so
I
have learned something new today!

I can see the logic of your suggested solution, but I can only get it
to
bring back a #VALUE error, could this be because we are trying to
amend the
text in a range as well as a single cell?

cheers

jb

"Roger Govier" wrote:

Hi

The tilde ~ is used as a wild card.
You could use something like
=VLOOKUP(SUBSTITUTE(A1,"~","^^"),SUBSTITUTE(J1:K10 0,"~","^^"),2,0)

where you are substituting the tilde with something that is unlikely
to
be found within the text entries of column J.

--
Regards

Roger Govier


"JB2010" wrote in message
...
Hi

I think i must have missed something in my Excel learning...

What's the deal with the "~" symbol?

if you try to look something up & the text has one of those in it,
it
messes
up the vlookup.

is this a pretty standard thing that has just passed me by all
these
years?
is there anyway round it?

cheers

jb