Help writing formula to flag text strings with certain words

joedacus

New Member
Joined
Jan 24, 2005
Messages
6
I am filtering a large database of addresses before doing a mailout. I have a list of about 30 words in column A (A2:A31) that, if any are present in the organization's name (C2:C27365), I do not want to mail to them.

How do I write a formula that goes through the list of the organization's names (C2:C27365) to 1) see if it has any of the words from the filter list (A2:A31), and 2) place an "x" (or TRUE or FALSE or anything to alert) in the cell to the immediate left (Column B) to flag the presence of a filter word? That way I can autofilter Column B and then delete all the rows with an "x" in Column B.

Your help greatly appreciated!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Help writing formula to flag text strings with certain w

Hi,

You could try:

=IF(OR(ISNUMBER(SEARCH($A$2:$A$31,C2))),"x","")

Confirmed with Ctrl + shift + enter in B2, then dragged down.
 

joedacus

New Member
Joined
Jan 24, 2005
Messages
6
Re: Help writing formula to flag text strings with certain w

Thanks, but I couldn't get it flag ("x") anything except four organizational names that did not have anything in the filter list.

Is there a function that reads a text string for either a single word or, ideally, an entire list from an array, and then puts an "x" or TRUE or FALSE in the adjacent cell if one is present?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Help writing formula to flag text strings with certain w

I do not know why it did not work for you.

Did you remember to confirm the formula with Ctrl + Shift + Enter?
Book1
ABCD
1
2catxIt'sacar
3dogxboatdog
4car Notthis
5boatxButthiscar
6train
Sheet1
 

joedacus

New Member
Joined
Jan 24, 2005
Messages
6

ADVERTISEMENT

Re: Help writing formula to flag text strings with certain w

I can't tell you how much I appreciate your help. I have a database of 27,000+ names that have to be filtered. We're close.

1. Regarding "confirming" the formula, after pasting the formula into column B, I press Ctrl+Shift+Enter, then drag the cells down to copy the formula. I'm not familiar with the term "confirming" so tell me if this correct.

2. The formula is almost working...for some reason it only refers to the adjacent left cell the formula is in (if the formula is pasted in and cell B7 is selected, when I check "Trace Precendents" button, only cell A7 is referenced). Looking at the formula as you have it, I can see that it refers to $A$1:$A$31. Any thoughts? Does it have to do wih confirming?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Help writing formula to flag text strings with certain w

Hi,

1.
What I mean is:
Select cell B2.
Paste or write the formula in the formula bar.
Then, while cursor is still in the formula bar you press Ctrl + shift + enter simultaneously.
If done corectly you will now see the formula surrounded by curly brackets as in my exhibit.
Now you can drag the formula down.

Ctrl + shift + enter makes the formula an array formula. Read more about them in help file.


2.
When I press trace predecendants an arrow points to one cell in A column and A2:A31 gets a blue border. If that happens the formula is ok.



You say it is almost working. What is the error you get?
 

joedacus

New Member
Joined
Jan 24, 2005
Messages
6

ADVERTISEMENT

Hi, again, thank you for your help. With 27,000+ names to filter through, I'm dying to find a final solution.
I got the CTRL+Shift+Enter part (I didn't have the cursor in the formula bar).

The error that keeps occuring in my project, as well as my reconstruction of your simple dog-cat-train model, is the formula in Column B only refers to the adjacent cell in Column A. If cell B12 is selected, and the formula as written and confirmed is in the formula bar, when I click on Trace Precedents, it selects the $A$2:$A$31 with a blue bounding box, but a horizontal line goes from C12 to B12 and another one starting from A2 to B12. The second tracer is the same in all cells in Column B--they all have a tracer starting at A2 that points to selected cell in Column B.

Also, all cells in Column B with the formula in it have an "x" mark in Column B, even when the organization's name in Column C does not contain any of the filter words in Column A.

I tried to post a screenshot using Colo' HTML but got a bunch of garbage in the text. I can email you a screen shot if you would prefer.

If you can solve this, I'll send you a bag of the latest Starbuck's Black Apron coffee. This has turned out to be a bear. Thank you for your help.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Help writing formula to flag text strings with certain w

It sounds to me your formula is entered corectly but just to be sure, do you see the curly brackets?

One reason tto why all gets X marked could be if you have empty cells in A2:A31. If that is the case, change formula to

=IF(OR(ISNUMBER(SEARCH(IF($A$2:$A$31="","zzzzzzzz",$A$2:$A$31),C2))),"x","")

Do not forget Ctrl + shift + enter.



This is another option that you do not need to ctrl + shift enter.
See if this works better:


=IF(SUMPRODUCT(LEN(C2)-LEN(SUBSTITUTE(C2,$A$2:$A$31,""))),"x","")
 

joedacus

New Member
Joined
Jan 24, 2005
Messages
6
YES! You did it! Tell me where to send the coffee. Email me at joedacus@ev1.net

I know I've said it several times before but I can't begin to tell you how much I appreciate this. I'm behind on getting bids because it's taken me days instead of hours to figure this out.

Thank you very much. I'm serious about the coffee.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Re: Help writing formula to flag text strings with certain w

Glad I could help!

Thanks for the offer, I do like coffee but I will not have you send it across the ocean...

Maybe I'll drop by on a cup if I ever come to your part of the world instead.

(y)
 

Forum statistics

Threads
1,148,529
Messages
5,747,230
Members
424,070
Latest member
smanni3

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