Search for string in cells across 4 columns

MikeO

Board Regular
Joined
Jun 17, 2009
Messages
57
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

paulrit

Active Member
Joined
Apr 19, 2009
Messages
318
you can put this formula in d1 and copy it down.

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

Regards,
Paul
 

MikeO

Board Regular
Joined
Jun 17, 2009
Messages
57
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"?
 
Last edited:

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

change to

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

paulrit

Active Member
Joined
Apr 19, 2009
Messages
318
another approach

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

Paul
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top