CODEWITHSUNDEEP
excelexcel-formula
Kyle Dama
Kyle Dama

Reputation: 49

Excel Formula If Cell Contains String

I'm currently working in excel, and I'm trying to figure out a way to find if multiple cells contain the string value of another cell, and if it does highlight the cell where the row and column meet up. I created an example of what I want, only it will be on a much larger scale.

Excel Image

I've tried using: =ISNUMBER(SEARCH(substring,text)) but I'm not quite sure how to use it the way I want to.

Any help will be appreciated!

Upvotes: 0

Views: 3456

Answers (2)

Werrf
Werrf

Reputation: 1148

Your formula will work nicely; what you'll want to do is put that formula into all the cells you want to highlight, so you get FALSE and TRUE in every cell.

You'll then use two Conditional Formatting rules. The first will look for Cell Value = TRUE, and will set cell background and font colour to yellow. The second will look for Cell Value = FALSE, and will set cell background to No Colour and Font to White.

This will reproduce the result you're looking for.

Edited to add:

It is possible to do this using just Conditional Formatting too, but it's a little more fiddly. If you want to try it, you can do this:

  1. Highlight your range, and take note of which cell is Active - that's the cell within your highlighted range that is still white. It's also the one whose address is shown in the Name box in the upper left. For the sake of this answer, we'll assume that's B2

  2. Create a new Conditional Formatting rule. Choose "Use a formula to determine which cells to format".

  3. Use the formula =ISNUMBER(SEARCH(B$1,$A2). Set the format to colour just the cell background.

Note where the $ appears in the formula above - you want to leave the row number anchored in the first part, and the column letter anchored in the second part.

This takes advantage of the fact that Conditional Formatting is able to use absolute, relative, and mixed references to find which cells to format. It's also a tidier solution, but it can be harder to maintain if the sheet is ever repurposed or modified.

Upvotes: 2

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

Your approach is correct, we can use the fact that conditional formatting is applied like dragging a formula, adapting relative references.

Create a conditional formatting formula rule:

=ISNUMBER(SEARCH(B$1,$A2))

Applied to B2:D7

Upvotes: 3

Related Questions