Solved

# Search Excel cells for multiple words

Posted on 2009-05-01
2,357 Views
I have an interesting issue with some logs that I port over ot excel workbooks for review.
I am working with Citrix application logs and am trying to search the description fields for several words.

If one of the words is present than put that word in a seperate cell on a different worksheet.

As an example,

You have an exel doc with two workbooks.
place a sencene in workbook 2 in A1.  The lazy dog barked.
place a sencene in workbook 2 in A2.  The brown dog barked.

in workbook1 in A1 have a formula that searches workbook2!A1:A2 for lazy and brown

in workbook1 A1 returns lazy
in workbook1 A2 returns brown
0
Question by:lstanton38

LVL 15

Expert Comment

Do you have a question?  If it's about why this happens, please post the formula.
0

LVL 4

Expert Comment

I think what you mean is that in sheet1, A1 would have a formula like =MyFormula(sheet2!A1, "lazy,brown")
Below that A2 would have a formula like =MyFormula(sheet2!A2, "lazy,brown")

You can do this with a custom function written in VBA. You can't do it with standard Excel because it requires to steps neither of which standard Excel provides.

Step1: Split "The lazy brown dog barked" into words & also split the search phrase "lazy, brown" into words. Excel for some reason still doesn't provide a function to split a phrase into words based on a separator.
However with some convoluted thinking we can bypass this shortcoming.
But the killer is Step2: Excel can't loop thru both sets of strings this way.
Basically you need to to search for lazy, then brown, then xyz.
You can use Find or Search to search for lazy in "The lazy brown dog barked", but then you can't repeat it for brown
I thinks it's pretty easy to do with a custom function tho
0

LVL 1

Author Comment

Here is what i have.  I am looking at 10 cells in a column.  There are 4 words that i need ot look for.  If one of those words is in the cell. That word gets display.
The words are:
LaserJet
Ricoh
OfficeJet
Deskject

What I want to acomplish is to have an IF statement or lOOKUP that searches through the test in Column E for the wrods and then display to one that is there.  There will only be one for each row in the column.

``````=IF(COUNT(SEARCH("DESKJET",E7)),"DeskJet","")
``````
PrinterLogErrorsWMSTCTX15.xls
0

LVL 4

Accepted Solution

Since you only have 4 seach words you can just use a nested if like below:
=IF(ISNUMBER(SEARCH("LaserJet",E5)),"LaserJet",IF(ISNUMBER(SEARCH("Ricoh",E5)),"Ricoh",IF(ISNUMBER(SEARCH("OfficeJet",E5)),"OfficeJet",IF(ISNUMBER(SEARCH("DeskJet",E5)),"DeskJet",""))))

If you need to add any more search words, use this formula, & paste it over the last "" in the formula above.
IF(ISNUMBER(SEARCH("SomeThing",E5)),"SomeThing","")

If you want something more flexible it can be done in VBA but it would be slower for large files.
0

## Featured Post

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
We are happy to announce a brand new addition to our line of acclaimed email signature management products – CodeTwo Email Signatures for Office 365.
To show how to create a transport rule in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Mail Flow >> Rules tab.:  To cr…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager