Search Excel cells for multiple words

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
LVL 1
lstanton38Asked:
Who is Participating?
 
r0bertdenir0Connect With a Mentor Commented:
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
 
zelron22Commented:
Do you have a question?  If it's about why this happens, please post the formula.
0
 
r0bertdenir0Commented:
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
 
lstanton38Author Commented:
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","")

Open in new window

PrinterLogErrorsWMSTCTX15.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.