Results 1 to 5 of 5

Thread: wildcard help

  1. #1

    wildcard help

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm using a formula to search a large column of data and recognize the text phrase 'check-in'. see below:

    =IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"Completed")>0,"Done",IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"In Progress")>0,"In Progress",""))

    It works great, but I recently discovered a bug. The wildcard "*"&F$2&"*" searches for the text phrase 'check-in' as typed in cell F2. The reason for the current wild card is sometimes "check-in" is followed or preceded by numbers or text in the column being searched (C:C). Example 123check-in(11-12).

    However, I've discovered that sometimes 'check-in' is missing the hyphen, so xxxcheck-inxxx can also show as xxxcheck inxxx in columnC:C. The current wildcards don't recognize this, is there a way to modify the current wildcards to find this? Or another solution?

    I changed cell F2 to say only 'check', but this now confuses 'check out' with 'check-in'.

    Thank you!
    Last edited by FreeOT; 2015-04-27 at 05:04 AM.

  2. #2
    Put check?in in F2

  3. #3
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Excel Version
    in cell F2 = put check*in

  4. #4
    F2 is one of the headers of a report, and too many downstream users would be left scratching their heads as to what check?in means. But I get your point, and if I created a hidden cell with check check?in and made that the new reference. Works great. Thanks!

  5. #5
    I hope that you referenced the original cell in your hidden cell with the SUBSTITUTE function, =SUBSTITUTE(F2,"-","?"), so as to keep down any maintenance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts