Search for string in cells across 4 columns

[MikeO]

Hello again - I need some more help!

I have 20,000+ rows of data with 4 columns. The columns represents attempts that I'm trying to quantify if they were successful or not. There are various text strings that can be in these cells, but the ones I am interested in all start with "Call".

My goal is to put a column to the right of my data with a "yes" if any of the 4 columns per row contain "Call" and a "No" if none of them do.

I'm thinking that I can just sort 3 of the columns alphabetically and do it sort of manually that way, but I'm wondering if there is a formula that could make this work. If so, I'm assuming it would be a very long "or" statement, or I would need to do a search for a string formula for each of the 4 columns independently, and then do another forumula to see if any of those 4 have a "yes" in them.

If sorting and doing it manually is easier, I'll just do that. Thanks.

 

[paulrit]

you can put this formula in d1 and copy it down.

=IF(COUNTIF(A1:D1,"call")>1,"yes","no")

Regards,
Paul

 

[texasalynn]

does this work

Excel Workbook
	A	B	C	D	E
1	Interested	Shopping	Completed	Out of town	Yes to any?
2	Yes				Yes
3			Yes		Yes
4				Yes	Yes
5	No				No
6					No
7
Sheet2

 

[MikeO]

I should have been more specific. The possible strings that I'm looking for say stuff like "Call completed" or "Call transferred". It always starts with Call but always has more after it. The ones I'm not interested in (that should yield a "no") start with other words

you can put this formula in d1 and copy it down.

=IF(COUNTIF(A1:D1,"call")>1,"yes","no")

Regards,
Paul

Is there a way to modify this so it will return a yes regardless of what appears after "Call"?

 

[texasalynn]

change to

=IF(COUNTIF(A2:D2,"*Call*")>0,"yes","no")

 

[MikeO]

You all are awesome, thanks!

 

[paulrit]

another approach

=IF(OR(LEFT(A1,4)="call",LEFT(B1,4)="call",LEFT(C1,4)="call",LEFT(D1,4)="Call"),"yes","no")

Paul