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!
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: