lstanton38
asked on
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
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
Do you have a question? If it's about why this happens, please post the formula.
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
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
ASKER
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.
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","")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.