Functions do not work the cell contains "~" sign. Is any "magic" meaning to this sign??
1. Can not find the cell with "vlookup"
2. Can not filter cells contain this sign.
Please advise.
Functions do not work the cell contains "~" sign. Is any "magic" meaning to this sign??
1. Can not find the cell with "vlookup"
2. Can not filter cells contain this sign.
Please advise.
Re: Functions do not work the cell contains "~" sign??
Yes, it's a magic tilde
From the help
Quoteyou can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
So unless you have an actual ? or a * in your data, you'll get a #NA result.
Strange though, I can filter a tilde perfectly ok....
Re: Functions do not work the cell contains "~" sign??
Concatenating 2 tilde characters in quotes does actually find the tilde in the lookup. Assuming that you have a single tilde in your string with characters before and after, the formula below looks up the value in A1 in the table C1:D3
=VLOOKUP(LEFT(A1,FIND("~",A1)-1)&"~~"&MID(A1,FIND("~",A1)+1,LEN(A1)-FIND("~",A1)),C1:D3,2,FALSE)
It's a bit more complex than a standard VLookup, but at least it does the job.
Hope this helps.
Regards,
Batman.
Re: Functions do not work the cell contains "~" sign??
Hi, Guys.
Thank you for you posts, I have found in Microsoft site some nice solution: http://support.microsoft.com/default.aspx?scid=kb;en-us;214138
Needs to replace "~" by "~~" , by "Replace" dialog box, and then to put the function.
Best regards..
Re: Functions do not work the cell contains "~" sign??
forum.ozgrid.com/index.php?attachment/71851/Hello
I hope that if I post a reply it goes back to the top ... of the list...
I have this same problem, but do not want to replace the tilde. I can not make sense of the formula Batman posted. Please advise on the attached document.
Sheet in document: "SubstoreProductMaster/Description (B2; B27 etc)" is reading from "SubstoreBuyPlan/PastelCode"
Regards,
Anri
Don’t have an account yet? Register yourself now and be a part of our community!