CONTAINS or other Function?

[slam]

I'm trying to determine if any of the text in H2 is a match for the entire contents of D2, and if it is, display Yes, else display No. The problem with this formula is that D2 is usually just text in quotes and not a cell reference, so I'm not sure if it can be modified, or if I should be using something else.

=IF(CONTAINS(H2,D2,TRUE),"Yes","No")

Any help would be greatly appreciated!

 

[Joe4]

Can you please show us an example of what is in cells D2 and H2, and show us one that would qualify as a match?

 

[Vogateer]

Would this do what you want?

=IF(ISNUMBER(SEARCH(D2,H2)), "Yes", "No")

You could replace D2 with text in quotes and it should work fine.

 

[slam]

Would this do what you want?

=IF(ISNUMBER(SEARCH(D2,H2)), "Yes", "No")

You could replace D2 with text in quotes and it should work fine.

Was just coming to post exactly this, as I figured out this was a better function indeed. Thank you!

 

[accessbob]

I don't know of a function called CONTAINS, unless that is a custom VBA function. But here's how I would do it:

=IF(ERROR(IF(AND(SEARCH(H2,D2)>0,LEN(D2)=LEN(H2)),"Yes","No"))

 

[Joe4]

I don't know of a function called CONTAINS, unless that is a custom VBA function. But here's how I would do it:

=IF(ERROR(IF(AND(SEARCH(H2,D2)>0,LEN(D2)=LEN(H2)),"Yes","No"))

Welcome to the Board!

Take a look at the function Vogateer posted. It is shorter and more efficient.

 

[accessbob]

Welcome to the Board!

Take a look at the function Vogateer posted. It is shorter and more efficient.

However, their function does not account for the requirement that the value they are wanting to search for in the other cell is the entire value.
any of the text in H2 is a match for the entire contents of D2

 

[Joe4]

However, their function does not account for the requirement that the value they are wanting to search for in the other cell is the entire value.
any of the text in H2 is a match for the entire contents of D2

Sure it does.

Here is the requirement:

if any of the text in H2 is a match for the entire contents of D2

Another way of saying that is this:
"Is the entire entry in cell D2 found anywhere in cell H2"?

So all you have to do is Search for D2 within cell H2, and if found, it will return a number (otherwise it returns an error).
That is what that formula does.

I admit I found the original question a bit confusing, and had to read it a few times myself, to fully understand what was being asked.
That is why I originally asked for an example, to confirm I was understanding it correctly.
However, the fact that they accepted that formula as the solution seems to confirm that is indeed the case.

 

[Cubist]

However, their function does not account for the requirement that the value they are wanting to search for in the other cell is the entire value.
any of the text in H2 is a match for the entire contents of D2

It looks like a miscommunication from the OP. If they want to match exactly the entire content you can simply check =IF(H2=D2,....).
The word choice "contains" suggests the OP is looking for a partial match so it's not necessary to check the length of the string.

 

[slam]

Hi all, to clarify with an example:

D2: US36
H2: T1G.0191.51.8.5 (US03), 6300004210 (CH12), 6300004268 (CH05), 6300004212 (US36)

Result in this case should be "Yes" because "US36" is in H2.