wildcard help

FreeOT

New member
Joined
Mar 19, 2015
Messages
14
Reaction score
0
Points
0
Hello.
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:
Put check?in in F2
 
Hi,
in cell F2 = put check*in
 
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!
 
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 :)
 
Back
Top