Functions do not work the cell contains "~" sign??

  • Re: Functions do not work the cell contains "~" sign??


    Yes, it's a magic tilde


    From the help


    Quote

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

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!