Partial Text search

12Rev79

New Member
Joined
Mar 2, 2021
Messages
30
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Thanks in advance.
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,599
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 2, 2021
Messages
30
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,599
Office Version
  1. 365
Platform
  1. Windows
You're welcome but I really didn't do anything as your formula already did what you wanted. :)
 

12Rev79

New Member
Joined
Mar 2, 2021
Messages
30
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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

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
Top