Formula to find any words from Col A in Col B (on same row) and confirm yes / no in Col C

ellison

Active Member
Joined
Aug 1, 2012
Messages
337
Office Version
  1. 365
Platform
  1. Windows
Hi, I'm trying to find a formula to check whether any search terms in Col A (which would be separated by spaces), are contained in in Col B (words also separated by spaces), and put a "yes" or "no" in Col C
In the example below, Col A is the data to be cleaned up, Col B is the Categories to be searched, & Col C = "are any words in Col A contained in Col B".
I've tried all sorts of helper columns and FIND and SEARCHES, but I'm wondering if there is a better way with a single formula!?
icon10.png


Col A: Data To be Cleaned
Col B: Categories
Any words from Col A contained in Col B?
ball and socket
balls, joints, bearings
yes
belt on car
belting
yes
tube for valve
pipe and tube
yes
regulators, valves
adapters
no
air cond'g pipe
pipes and exhausts
yes

<tbody>
</tbody>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Maybe:

ABCD
1Col A: Data To be CleanedCol B: CategoriesAny words from Col A contained in Col B?
2ball and socketballs, joints, bearingsyesYes
3belt on carbeltingyesYes
4tube for valvepipe and tubeyesYes
5regulators, valvesadaptersnoNo
6air cond'g pipepipes and exhaustsyesYes

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet8

Array Formulas
CellFormula
D2{=IF(OR(IFERROR(FIND(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),{1,100,200,300,400},50)),"|"&B2)>1,FALSE)),"Yes","No")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This should work if column A has up to 5 words in it.
 
Upvote 0
Absolutely perfick - I substituted in "search" for the "find" in the formula to make sure it didn't matter about upper or lower case - but again .... ABSOLUTELY PERFICK!

You're a gent!!!!!

Best
Neil
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top