Extracting consecutive letters from a cell and applying the IF function

[searnold56]

Hello everyone,

First timer here. I'm working on an email database and am looking to sort them into type. I am trying (without success) to use the IF function as follows.

If A1 contains the letters "isd" consecutively, then B1=TRUE
If A1 contains the letters "google", "yahoo", "hotmail", or "austin.rr" consecutively, then B2=true

HELP!

 

[CodeNinja]

Probably not the most efficient formula, but this will work...

B1: =FIND("isd",A1)>0
B2: =OR(IFERROR(FIND("Hotmail",A1)>0,FALSE),IFERROR(FIND("Yahoo",A1)>0,FALSE),IFERROR(FIND("Google",A1)>0,FALSE),IFERROR(FIND("austin.rr",A1)>0,FALSE))

 

[Rick Rothstein]

Assuming you also want FALSE when one of those text string is not in the cell, put these formula in the indicated cells...

B1: =COUNTIF(A1,"*isd*")>0

B2: =COUNTIF(A1,"*google*")+COUNTIF(A1,"*yahoo*")+COUNTIF(A1,"*hotmail*")+COUNTIF(A1,"austin.rr")>0

 

[TheCman81]

What about

=SUM(COUNTIF(A1,{"*google*","*yahoo*","*hotmail*","*austin.rr*"}))>0

 

[searnold56]

Assuming you also want FALSE when one of those text string is not in the cell, put these formula in the indicated cells...

B1: =COUNTIF(A1,"*isd*")>0

B2: =COUNTIF(A1,"*google*")+COUNTIF(A1,"*yahoo*")+COUNTIF(A1,"*hotmail*")+COUNTIF(A1,"austin.rr")>0

Ended up with:

=OR(NOT(ISERR(FIND("isd",$A19,1))),NOT(ISERR(FIND(".edu",$A19,1))))
&
=OR(NOT(ISERR(FIND("yahoo",$A19,1))),NOT(ISERR(FIND("gmail",$A19,1))),NOT(ISERR(FIND("hotmail",$A19,1))),NOT(ISERR(FIND("att",$A19,1))),NOT(ISERR(FIND("austin.rr",$A19,1))))

Tedious, but it's the only one I could get to work. Thank you for the input!