Find whether part of a cell matches with another entire and exact cell

chabuka

New Member
Joined
Jan 28, 2019
Messages
3
Hi,

firstly thank a lot for reading and hopefully answering my question.
I need to find cells that within their string contain the content of a different cell. I think I'm having trouble because I need to refer back to the cell with the string and I'm failing miserably at it. Here the situation:

Column A has a suggestion of possible "genes" and looks like this:
B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN
P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN

Column B is a list of genes like:
NB5R3
LMNB1
PRDCX
LM

You will find that cells in column A have many suggestions for the right type of gene (the genes are always hidden inbetween "|" and "_" if that helps with the formula) and column B has the genes that I'm interested in. If any one of the genes in B:B is part of a cell in e.g A1, I need to know that. Any form of highlighting (TRUE/FALSE, Yes/No, COUNT or other) would help.
I can easily highlight cells in B that contain anything from A (=COUNTIF(A:A,"*"&B1&"*")), but with limitations and I didn't manage to do it the other way around which is actually more helpful.

I have tried quite some formulas, e.g.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B:B,A1))),"Yes","No")
=IF(SUMPRODUCT(--MATCH(SEARCH(B:B,A1))),"Yes","No")

but they all were error-prone. I think it has got to do with problems arising from short gene names (e.g. LM) that collide with other gene names (e.g. LMNB1).
I need to find a way to refer to finding the exact content of any of the B cells anywhere within a certain cell in e.g. A1 without finding wrong positives.
I hope this explanation was ok. Thanks in advance, I really apreciate your help!
Lina
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Maybe a VBA to highlight cells in Col A
Code assumes cols A & B and both start at row 1


Code:
Sub MM1()
Dim lr As Long, r As Long, x As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lr
    For x = 1 To 4 'change col B range to suit
        If InStr(Range("A" & r).Value, Range("B" & x).Value) <> 0 Then
            Range("A" & r).Interior.ColorIndex = 3
        End If
    Next x
Next r
End Sub
 
Upvote 0
(This does not answer your question but may be helpful)

Your original formula would not distinguish between NB5R3 and NB5R33 and ZNB5R3 and therefore could result in false positives
=COUNTIF(A:A,"*"&B1&"*")

Try this instead
=COUNTIF(A:A,"*|"&B2&"_*")
 
Upvote 0
maybe something like this
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Yes / No[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN[/td][td=bgcolor:#E2EFDA]NB5R3[/td][td=bgcolor:#E2EFDA]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN[/td][td]LMNB1[/td][td]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN[/td][td=bgcolor:#E2EFDA]PRDCX[/td][td=bgcolor:#E2EFDA]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN[/td][td]LM[/td][td]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN[/td][td=bgcolor:#E2EFDA]NB5R3[/td][td=bgcolor:#E2EFDA]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN[/td][td]LMNB1[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN[/td][td=bgcolor:#E2EFDA]PRDCX[/td][td=bgcolor:#E2EFDA]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN[/td][td]LM[/td][td]Yes[/td][/tr]
[/table]


if you are able to use PowerQuery
 
Last edited:
Upvote 0
Here is one way

In C2 and copy down to get EXACT string only
="|"&B2&"_"

In D2 and copy down
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$6,A2))),"Yes","No")

BUT C2:C6 cannot contain any blank cells
- becomes a problem to vary the number of items
- one way would be to use a bigger range than required and put dummy values in the extra cells
eg to allow for 19 items $C$2:$C$20 and fill $C$7:$C$20 with ZZZZZ (ie something that would not be found)

A more elegant way would be to use a dynamic range named MyRange, with RefersTo formula
=OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C$2:$C$20))

and in E2 copied down
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(myRange,A3))),"Yes","No")

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Possible[/td][td]Looking for[/td][td]Exact string[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN[/td][td]NB5R3[/td][td]|NB5R3_[/td][td]Yes[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMAN[/td][td]LM[/td][td]|LM_[/td][td]Yes[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LM_HUMAN[/td][td]PRDCX[/td][td]|PRDCX_[/td][td]Yes[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LX_HUMAN[/td][td]LMNB1[/td][td]|LMNB1_[/td][td]No[/td][td]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB11_HUMAN[/td][td]NB5R33[/td][td]|NB5R33_[/td][td]No[/td][td]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN|LM_HUMAN[/td][td][/td][td][/td][td]Yes[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM_HUMAN[/td][td][/td][td][/td][td]Yes[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM44_HUMAN[/td][td][/td][td][/td][td]No[/td][td]No[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Last edited:
Upvote 0
Hi all,

many, many thanks for your help!
Yongle, this is exactly what I need. I have tried it and it works a treat. In the past I was trying to split the cells in A into many cells (text to columns) but that was very messy as sometimes there is one gene suggestion and sometimes up to 25. The way how you suggest it with additional columns C and D will be entirely sufficient as my list in B is always the same without any blank cell danger. But I'm sure someone reading this post will need that in the future.
Sandy, Michael, thank you for your suggestions also! But not being familiar with PowerQuery and very rudimental on VBA, I will go the path of least resistance with Yongle's proposed formulas.

I really appreciate you all taking the time to help others. You have made my excel life much easier.

Best regards,
Lina
 
Upvote 0
Hi,

Here's another way, no helper column needed:


Book1
ABC
1PossibleLooking for
2B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMANNB5R3Yes
3P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB1_HUMANLMYes
4P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LM_HUMANPRDCXYes
5P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LX_HUMANLMNB1No
6P02545-2|LMNA_HUMAN:P02545-6|LMNA_HUMAN:P02545|LMNA_HUMAN:E9PBF6|E9PBF6_HUMAN:P20700|LMNB11_HUMANNB5R33No
7B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R3_HUMAN:P00387|NB5R3_HUMAN:P00387-3|NB5R3_HUMAN|LM_HUMANYes
8B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM_HUMANYes
9B1AHF3|B1AHF3_HUMAN:P00387-2|NB5R44_HUMAN:P00387|NB5R44_HUMAN:P00387-3|NB5R44_HUMAN|LM44_HUMANNo
Sheet510
Cell Formulas
RangeFormula
C2=IF(ISNUMBER(LOOKUP(2,1/SEARCH("|"&B$2:B$6&"_",A2))),"Yes","No")


Formula copied down.
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

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