• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2361
  • Last Modified:

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
0
lstanton38
Asked:
lstanton38
  • 2
1 Solution
 
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
 
r0bertdenir0Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now