8

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?

niton
  • 1,832
socksxbirkenstocks
  • 95

2 Answers2

14

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).

Jerry
  • 4,956
7

Clever Answer Jerry. Here is Further Explaination,

Microsoft Excel uses the tilde (~) as a marker to indicate that the next character is a literal. When you use the Find and Replace dialog box to find or replace a character such as a tilde (~), an asterisk (*), or a question mark (?), you must add a tilde (~) before the character in the Find what box.

How to find and replace tildes and wildcard characters in Excel

Scheballs
  • 413