Help with formula to match text

[pingman3]

Trying to figure out a formula to search the first row of text in column A for a match to the 'last name' in column b. If 'last name' from column b is included in the first row of text from column a, display a 'yes' in column d. If 'last name' from column b is not included in the first row of text from column a, display a 'no' in column d.

See attached example of spread.

Not sure if this 'search first row of text in a cell' can be accomplished with formula vs. VB.


Thank you.

[protonLeah]

Please Login or Register  to view this content.

* CHAR(10) =new line code

[pingman3]

Thank you. That worked really well. A follow up question after looking at my test data.

I added a column for 'Email address' (column D). Is it possible to add an 'or' condition to your formula to identify if there is an email domain match?

ie. first line of text in column A is: Entered on 10/09/2020 at 5:02:00 PM CDT (GMT-0500) by [email protected]:

Email address is column D is: [email protected]

Would expect a 'Yes' since testdomain is in first line of text in column A. Attached a new spread sample. Thanks again for helping.

[Pepe Le Mokko]

No sample attached

[pingman3]

Attaching new version

[Glenn Kennedy]

Try this:

=IF(OR(ISNUMBER(SEARCH(MID(D2,SEARCH("@",D2)+1,255),LEFT(A2,FIND(CHAR(10),A2)))),ISNUMBER(SEARCH(B2,LEFT(A2,FIND(CHAR(10),A2))))),"Yes","No")

[Bo_Ry]

Please try

=IF(COUNT(INDEX(SEARCH(MID(B2:D2,FIND({"",0,"@"},B2:D2),20),LEFT(A2,FIND(CHAR(10),A2))),)),"Yes","No")

[pingman3]

Thanks, that worked great. One last request. How can I return blank of A2 is blank? Currently, if I copy that formula down an entire column, it returns no even if no data is present in cells A:D.

[protonLeah]

=if(isblank(A2),"",IF(OR(ISNUMBER(SEARCH(MID(D2,SEARCH("@",D2)+1,255),LEFT(A2,FIND(CHAR(10),A2)))),ISNUMBER(SEARCH(B2,LEFT(A2,FIND(CHAR(10),A2))))),"Yes","No"))

or
=IF(ISBLANK(A2),"",IF(COUNT(INDEX(SEARCH(MID(B2:D2,FIND({"",0,"@"},B2:D2),20),LEFT(A2,FIND(CHAR(10),A2))),)),"Yes","No"))

you could replace ISBLANK(A2) with IF(COUNTA(A2:D2)=0, ...