CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Acidon
Acidon

Reputation: 1334

Conditionally format a cell which has part of its text match any value from a range of cells in a specific column

In excel 2010 I have 2 columns:

Column A has a list of urls, like:

http://domain.com/12345

http://domain.com/23445

http://domain.com/15321

http://domain.com/97754

Column B has a list of numbers like:

86860

12345

74663

23232

34556

23333

How do I highlight cells in column A which url contains the number that matches any number in in column B?

In other words I need each cell in column A to be checked against every cell in column B and to highlight all the cases where the url in cell from column A contains the number found in any of the cells in column B.

So only the first cell in column A in the example above will be highlighted because it contains the number 12345 from column B.

Thanks

Upvotes: 1

Views: 2401

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

Select entire column A and apply Conditional formatting with this formula:

=OR(ISNUMBER(FIND($B$1:$B$6,A1)))

where $B$1:$B$6 range of all numbers in column B

enter image description here

Result:

enter image description here

Upvotes: 4

Related Questions