Search for pattern in two columns

[arvindsaraswat]

I have a huge excel (running into 10k+ rows). The first row is title row.

I want to search for "Some text here : xxx" string in two columns (AA, BA) where xxx can be YES or NO or ??? or blank. This is not nicely formatted, so we can assume some text immediately after YES or NO Strings. Also, YES & NO are case insensitive.

Examples:
Some text here : YES
Some text here : Yesxxssdasf
Some text here : Yes sdfsd 342342 sdfsdfgSome text here : NO
Some text here : nozxsds

If the "Some text here : Yes" is present in AA or BA or both, mark column CA as "PRESENT". In all other cases, mark it as "ABSENT".

 

[FormR]

Hi, do you mean something like this maybe:

Excel 2013/2016
	AA	AB	AC
1	Some text here : YES		PRESENT
2	Some text here : Yesxxssdasf	Some text here : Yesxxssdasf	PRESENT
3	Some text here : Yes sdfsd 342342 sdfsdfgSome text here : NO		PRESENT
4	Some text here : nozxsds	Some text here : nozxsds	ABSENT
5	Some text here : nozxsds	Some text here : YES	PRESENT
Sheet1

Cell Formulas
Range	Formula
AC1	=IF(ISNUMBER(LOOKUP(1,-SEARCH(": yes",AA1:AB1))),"PRESENT","ABSENT")

 

[arvindsaraswat]

This seems to work. How to handle:
1. what if there is no space after ":" character YES, : yes ...)
2. My columns are AA and BA (this I have fixed by changing AA1:AB1 range accordingly). Though I dont want to search for intermediate columns.

 

[FormR]

Hi,

1. How about we just search for "yes".
2. I'd assumed that was a typo - how about this:

=IF(OR(ISNUMBER(SEARCH("yes",AA1)),ISNUMBER(SEARCH("yes",BA1))),"PRESENT","ABSENT")

 

[arvindsaraswat]

Excellent.. thats what I have done now.

While parsing, I found that someone has use "true" for "Yes"... so now I can extend it easily.

Thanks a lot.