Hi every body
I am new to excel.
I have a list of words (983) which I want to be searched within a cell.
I know that it may be a straightforward task. I actually found similar question but the answer could not help me.
I have attached a small part of my excel file.
First column contain list of words and second columns are the cells that must be searched.
I will appreciate any help
Nazanin
You don't indicate what the results should be. Your sample data does not appear to include any matches, either.
If you were to put this formula in C2 and copy down, the LAST word from column A that existed in the text string in column B will appear in C. Only one result per cell.
=LOOKUP(2, 1/(ISNUMBER(SEARCH($A$1:$A$16, B1))), $A$1:$A$16)
Last edited by JBeaucaire; 04-26-2015 at 12:29 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
thanks for the help. I actually know that words in column A are located in front of "GENE=" or "lOCUS=".
using your formula a lot of unwanted matches were found. for example for word "nasE" the row containing "dehydrogenase" word was reported as match.
(Some cells do not have any "GENE=" or "lOCUS="). My first priority is the match located in front of "GENE=" though.
Can you suggest me any formula that only searches the words in front of "GENE=" or "lOCUS="?
here is an example of a cell content in column B:
1121158 // gi|1121158|ref|NC_002758.2|NC_002758.2(GI:57634611):1234382-1236618(+) Staphylococcus aureus subsp. aureus Mu50, GENE=pbpA PRODUCT=penicillin-binding protein 1 LOCUS=SAV1181 // ncbi_bacterial // 20 // --- /// 1123855 // gi|1123855|ref|NC_002745.2|NC_002745.2(GI:29165615):1158053-1160289(+) Staphylococcus aureus subsp. aureus N315, GENE=pbpA PRODUCT=penicillin-binding protein 1 LOCUS=SA1024 // ncbi_bacterial // 20 // --- /// 3236102 // gi|3236102|ref|NC_002951.2|NC_002951.2(GI:57650036):1197639-1199875(+) Staphylococcus aureus subsp. aureus COL, GENE=pbp1 PRODUCT=penicillin-binding protein 1 LOCUS=SACOL1194 // ncbi_bacterial // 20 // ---
best
Nazanin
How about this:
1) Insert a blank row1
2) Put GENE= in D1 and put LOCUS= in E1
3) Put this formula in D2, copy down and across to column E:
=IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B2, " ", REPT(" ", 50)), FIND(D$1, SUBSTITUTE($B2, " ", REPT(" ", 50))), 50)), D$1, ""), "")
wow! thank you so much
yes, it worked. I could collect the words in front of "GENE=" and "LOCUS=". Now I just have to use VLOOKUP function to compare them with my word list.
Is there any advanced tutorial that can help me to get familiar with these kind of advanced functions? I think I really need to learn how to use excel for these kind of tasks
Nazanin
This forum IS your advanced lookup help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks