# Partial Text search

#### 12Rev79

##### New Member
Dear Experts,

I have this formula
=FILTER(FILTER('[RFI Log.xlsx]RFI'!\$A:\$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!\$AG:\$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})

that would search exact value from my Cell B1.

I want to be dynamic not just the exact value but also a partial text.

How would I modify my formula I have tried doing ISNUMBER(SEARCH("*"&B1&"*"

but still it's not giving me any values.

Please is anyone is kind to help me figure out.

Rev12

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### jtakw

##### Well-known Member
HI,

No way to figure out your situation, please post sample data with expected results.
Check my signature regarding posting via XL2BB, Thank You.

I probably can't help, as I don't have the FILTER function, but it would help other potential helpers for posting samples.

#### Peter_SSs

##### MrExcel MVP, Moderator
I have this formula
=FILTER(FILTER('[RFI Log.xlsx]RFI'!\$A:\$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!\$AG:\$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})

that would search exact value from my Cell B1.
No, your formula already searches for the B1 value as partial text in column AG of the other sheet.

Here is my dummy data for RFI

RFI Log.xlsx
ABCDENOPAG
1Hdr1Hdr2Hdr3Hdr4Hdr5Hdr14Hdr15Hdr16Hdr33
2ABCD20ABCD668ABCD669ABCD532ABCD749ABCD177ABCD2ABCD302ABCD578
3ABCD51ABCD973ABCD192ABCD630ABCD285ABCD516ABCD733ABCD464ABCD323
4ABCD887ABCD109ABCD318ABCD439ABCD364ABCD119ABCD590ABCD106ABCD951
5ABCD319ABCD670ABCD418ABCD422ABCD951ABCD593ABCD166ABCD12ABCD296
6ABCD786ABCD538ABCD495ABCD899ABCD232ABCD340ABCD653ABCD921ABCD187
7ABCD529ABCD126ABCD97ABCD635ABCD541ABCD877ABCD103ABCD241ABCD364
8ABCD36ABCD160ABCD494ABCD107ABCD699ABCD554ABCD934ABCD738ABCD951
9ABCD32ABCD420ABCD350ABCD307ABCD136ABCD2ABCD760ABCD961ABCD387
10ABCD696ABCD654ABCD911ABCD350ABCD88ABCD729ABCD702ABCD997ABCD209
RFI

.. and my formula sheet with your formula from post #1. As you can see it has identified "CD95" as partial text in rows 4 & 8 and returned the relevant columns from those rows.

12Rev79.xlsm
BCDEFG
1CD95ABCD887ABCD318ABCD439ABCD119ABCD106
2ABCD36ABCD494ABCD107ABCD554ABCD738
3
Sheet2
Cell Formulas
RangeFormula
C1:G2C1=FILTER(FILTER('[RFI Log.xlsx]RFI'!\$A:\$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!\$AG:\$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
Dynamic array formulas.

#### 12Rev79

##### New Member
No, your formula already searches for the B1 value as partial text in column AG of the other sheet.

Here is my dummy data for RFI

RFI Log.xlsx
ABCDENOPAG
1Hdr1Hdr2Hdr3Hdr4Hdr5Hdr14Hdr15Hdr16Hdr33
2ABCD20ABCD668ABCD669ABCD532ABCD749ABCD177ABCD2ABCD302ABCD578
3ABCD51ABCD973ABCD192ABCD630ABCD285ABCD516ABCD733ABCD464ABCD323
4ABCD887ABCD109ABCD318ABCD439ABCD364ABCD119ABCD590ABCD106ABCD951
5ABCD319ABCD670ABCD418ABCD422ABCD951ABCD593ABCD166ABCD12ABCD296
6ABCD786ABCD538ABCD495ABCD899ABCD232ABCD340ABCD653ABCD921ABCD187
7ABCD529ABCD126ABCD97ABCD635ABCD541ABCD877ABCD103ABCD241ABCD364
8ABCD36ABCD160ABCD494ABCD107ABCD699ABCD554ABCD934ABCD738ABCD951
9ABCD32ABCD420ABCD350ABCD307ABCD136ABCD2ABCD760ABCD961ABCD387
10ABCD696ABCD654ABCD911ABCD350ABCD88ABCD729ABCD702ABCD997ABCD209
RFI

.. and my formula sheet with your formula from post #1. As you can see it has identified "CD95" as partial text in rows 4 & 8 and returned the relevant columns from those rows.

12Rev79.xlsm
BCDEFG
1CD95ABCD887ABCD318ABCD439ABCD119ABCD106
2ABCD36ABCD494ABCD107ABCD554ABCD738
3
Sheet2
Cell Formulas
RangeFormula
C1:G2C1=FILTER(FILTER('[RFI Log.xlsx]RFI'!\$A:\$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!\$AG:\$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
Dynamic array formulas.
Thank you appreciated, yes it works will so far.

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome but I really didn't do anything as your formula already did what you wanted.

#### 12Rev79

##### New Member
You're welcome but I really didn't do anything as your formula already did what you wanted.
Yes with your comments makes me re examine my formula and I fount out that it works. Thanks a lot.

Replies
15
Views
248
Replies
0
Views
67
Replies
0
Views
89
Replies
3
Views
74
Replies
1
Views
354

1,130,362
Messages
5,641,691
Members
417,229
Latest member
BODYCOTE

### 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?

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