Text search in Excel

[Petercheng]

Hi Folks,

I want to use Excel to perform some types of text search and do display the results as described below:

For example, I have an excel table with two columns (A, and B). column A contains a list of book names, and cells in column B are synopsis of the respective books (which is a text with many words). Now I want to use a key word to search the texts of synopsis in column B, and then display all book(s)(which is in column A), whose synopsis contains words matching the key word. If use Excel's FIND and REPLACE function, all matched synopsis in column B will be displayed after click FIND ALL. However, what I want is the corresponding book names in Column A. does anybody know if this is possible? Or are there any other softwares that have this type of function?

Your help is greatly appreciated!

Peter

 

[Firefly2012]

Hi

Try using Autofilter - if you apply it to your header in A1 then you can do a custom filter on column B with "contains" and enter your text string to be searched for

 

[Aladin Akyurek]

Hi Folks,

I want to use Excel to perform some types of text search and do display the results as described below:

For example, I have an excel table with two columns (A, and B). column A contains a list of book names, and cells in column B are synopsis of the respective books (which is a text with many words). Now I want to use a key word to search the texts of synopsis in column B, and then display all book(s)(which is in column A), whose synopsis contains words matching the key word. If use Excel's FIND and REPLACE function, all matched synopsis in column B will be displayed after click FIND ALL. However, what I want is the corresponding book names in Column A. does anybody know if this is possible? Or are there any other softwares that have this type of function?

Your help is greatly appreciated!

Peter

Filtering (with AutoFilter or Advanced Filter) is a good option.

Another option is the formula approach:

<TABLE style="WIDTH: 436pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=580><COLGROUP><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4266" width=120><COL style="WIDTH: 203pt; mso-width-source: userset; mso-width-alt: 9614" width=270><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4494" width=126><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 90pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=120>Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 203pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=270>Synopsis</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 95pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=126>Keyword</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>oblomov</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>onverschilligheid, apathie en hulpeloosheid</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Onverschilligheid</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>masumiyet müzesi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>herinneringen aan Istanbul, onverschilligheid</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>1984</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>closed life</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Titles</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>oblomov</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>masumiyet müzesi</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR></TBODY></TABLE>

In what follows, Titles stands for A2:A4 and Descriptions for B2:B4.

D2: Onverschilligheid (A keyword of interest.)

D3, just enter:

=SUMPRODUCT((ISNUMBER(SEARCH(D2,Descriptions))+0))

D5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$5:D5)<=$D$3,INDEX(Titles,
  SMALL(IF(ISNUMBER(SEARCH($D$2,Descriptions)),
   ROW(Descriptions)-ROW(INDEX(Descriptions,1,1))+1),
    ROWS($D$5:D5))),"")

 

[Petercheng]

Thanks a lot Firefly2012 and Aladin Akyurek for your prompt reply. I tried Autofilter, it works, but the formula approach not working in my hand (it returns 0, instead of 2. However, I have zero programming skill, and the failure is mostly likely coming from my mistake. I would appreciate greatly if Aladin can just email me the formula approach's excel file).

In fact, I am looking for something more user friendly: will it be possible to create something like Search Wizard in excel where one can enter key word to search the contents in specific columns, and then it returns a report (or sheet) that contain information on the cells related to the keyword-hit column(s). Aladin's formula approach seems to be a good start, but I am not sure if it is possible to create something I described above. Are there other software that can perform this type of search and reporting?

Thanks a lot for your help!

Peter Cheng

 

[Aladin Akyurek]

Thanks a lot Firefly2012 and Aladin Akyurek for your prompt reply. I tried Autofilter, it works, but the formula approach not working in my hand (it returns 0, instead of 2. However, I have zero programming skill, and the failure is mostly likely coming from my mistake. I would appreciate greatly if Aladin can just email me the formula approach's excel file).

In fact, I am looking for something more user friendly: will it be possible to create something like Search Wizard in excel where one can enter key word to search the contents in specific columns, and then it returns a report (or sheet) that contain information on the cells related to the keyword-hit column(s). Aladin's formula approach seems to be a good start, but I am not sure if it is possible to create something I described above. Are there other software that can perform this type of search and reporting?

Thanks a lot for your help!

Peter Cheng

See:

https://sites.google.com/site/aladinfiles/

for: aaComposeListWithSmall+If Petercheng.xlsx