SEARCH function only finds full string, but not substring

[richi5000]

Hi all,

Background: I was searching for a formula (for conditional formatting) that checks if a specific cell (text format) contains a specific text of an adjunctive cell as a substring. If there is no match, the cells should be highlighted red. As an example:

01	ABC_01_XXX
22	ABC_22_XXX
01	ABC_88_XXX

<tbody>
</tbody>

I want to check if column B contains the content of column A as a substring. So in the example the row 3 should be highlighted.

I've tried

=ISNUMBER(SEARCH($B$1;$A$1))

This did'nt work. When trying to crack down the issue, I tried parts of the formula to check, what goes wrong.

When I try to search for the substring manually:

=SEARCH($B$1;"01")

it returns "#VALUE"

Instead, when I try

=SEARCH($B$1;"ABC_01_XXX")

it works and returns "1"

But shouldn't the search-formula find the substring and return the position of the given text?


Thanks,
Richie

 

[Scott T]

Looks like you have your arguments switched

=SEARCH(find_text,within_text)

You want to look for A1 with in B1

=SEARCH(A1,B1)

 

[richi5000]

Thanks for the fast answer. That was quite easy - works great. Obviously that was a great example of monday morning sleep deprivation

btw..the final code for the conditional formatting is:

=NOT(ISNUMBER(SEARCH($A1;$B1)))

 

[Scott T]

Glad to help