vlookup & "~" symbol

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi

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

where you are substituting the tilde with something that is unlikely to
be found within the text entries of column J.
 
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)
 
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
 
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
 
Back
Top