Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Try using double tildes:

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

It seems to turn the second tilde to a literal tilde to lookup, but besides that, I don't know why it works.

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

share|improve this answer
1  
+1 Jerry - The range in VLOOKUP should presumably be A:B..... –  barry houdini Jun 3 '13 at 19:26
    
@barryhoudini oops, right. The data I was testing was actually another one ^^; –  Jerry Jun 3 '13 at 19:27
1  
=VLOOKUP("~"&C1,A:B,2,FALSE) seems a shorter version (for the example you gave). –  pnuts Jun 3 '13 at 19:36
1  
@pnuts Yes, it is, but it won't work for ~Hello~, right. –  Jerry Jun 3 '13 at 19:40
    
True - and that was the question! –  pnuts Jun 3 '13 at 19:40

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.