Excel text search question

[harumph]

I would like to use an excel spreadsheet as a searchable database, specifically to match sound-effect file names with their descriptions. My goal is to avoid forking out the money for software like Soundminer. What I want to do is pretty straightforward: type in a series of words in the search box, something like "footsteps female concrete," and then click next through all the rows that have a cell containing those words. Is there a way to set up the search box so that search is neither case sensitive nor word-order sensitive? For example, there might be a cell with this description: "Female footsteps. Walking on concrete." As of now, if I search "footsteps female concrete," I won't find that row. Not sure if it's possible, but it would be really convenient if it was. Thanks.

[dilipandey]

Hi harumph,

Welcome to the forum

Yes, we can achieve the same using two approaches.. 1) using search function of excel which is not case sensitive and will treat for example MAN, man, Man, mAN etc as one and 2) using vba (macro)

Post a sample data with all possible situations. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[harumph]

Here's an example: I might search something like "single prop airplane cessna." The word order will prevent ex search from returning any of the below.

FileName Description

Airplane9100-111 Airplane Single Prop Cessna Start Idle
Airplane9100-112 Airplane Single Prop Cessna Idle Shut Off
Airplane9100-113 Airplane Single Prop Cessna Taxi To Runway
Airplane9100-114 Airplane Single Prop Cessna Take Off

[darkyam]

There's a way to do it if you put each search term in a separate cell. Basically, it's using Search on each cell a term is in. You can expand the formula to accommodate a fairly large number of cells. If you make some of them blank, the formula will still work. Keep in mind that if a word in your phrase has the same letters, such as females, as one of your search terms, it will return a yes.