Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Working avec "~" in excel

ayi

I have a data set that has names that contain ~ within them. Unfortunately, I cannot find a way to filter or incorporate these cells in a formula.

For example, I tried to use a text cell that had ~ within the name, but I would receive a #N/A error. I know that my VLOOKUP formula works because the only errors I receive are with cells that contain ~ within them.

I even attempted to filter out these results, but excel would replace the filter and treat it like a wildcard filter.

My questions are:

How do I filter ~?
How do I use text cells that contain ~ in VLOOKUPS?

ayi

Try using double tildes:

=VLOOKUP(SUBSTITUTE(C1,"~","~~"),A:B,2,false)

The tilde is the special character in Excel to escape other special characters. Use a tilde to make the tilde you want to match a literal tilde (e.g. * in excel find matches any number of characters, but ~* will match a literal *).

The data I used it on is:

   A       B        C           D
~Hello     1      ~Hello       =VLOOKUP(SUBSTITUTE(C1,"~","~~"),A:B,2,false)
Hello      2

Cell D1 is returning me 1 (meaning it is matching ~Hello).