I have this info:
Mohd Abdul bin Razak
Fatima binti Nor
Tan Mae Ling
My formula is =IF(ISNUMBER(SEARCH("bin",B1)),"Male", ""). My question is how to construct in such a way that if B1 cell contains "bin" return is Male, if B1 contains "binti" answer is Female, if no binti and bin, just blank.
Thank you and Merry Christmas..
Last edited by jewellove; 12-26-2012 at 01:37 AM. Reason: edit
Please add another 3 criteria.
Return to Male if text contains "Bin", "Mohd" and "Md".. Then return to Female if text contains "Binti", If none of the words mention then answer is blank.. Thank you.
Try this:
=IF(OR(ISNUMBER(SEARCH(" bin ",B1)),ISNUMBER(SEARCH(" Mohd ",B1)),ISNUMBER(SEARCH(" Md ",B1))),"Male",IF(ISNUMBER(SEARCH(" binti ",B1)),"Female",""))
Quang PT
your formula before
=IF(ISNUMBER(SEARCH("bin";B1));"Male"; "")
change this with adding space after bin before last " to be:
=IF(ISNUMBER(SEARCH("bin ";B1));"Male"; "")
In C1 cell
Formula:
=IF(ISNUMBER(SEARCH("BIN",B1)),IF(ISNUMBER(SEARCH("BINTI",B1)),"Female","Male"),"")
Drag it down...
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Re: How to match cell with three text criteria?
I tried this formula of Bebo
=IF(OR(ISNUMBER(SEARCH(" bin ",B1)),ISNUMBER(SEARCH(" Mohd ",B1)),ISNUMBER(SEARCH(" Md ",B1))),"Male",IF(ISNUMBER(SEARCH(" binti ",B1)),"Female","")
It works for text containing bin, binti but not for Mohd and Md. Please Advice.... See working sheet for easy reference
Thank you for looking into my post. Happy Boxing Day
Try in D2
=IF(ISNUMBER(SEARCH("binti",B2)),"Female",IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(Male_List,B2))),"Male",""))
Somewhere on the sheet I made a named range titled Male_List with bin, Mohd, and Md.
HTH
Regards, Jeff
Try this:
=IFERROR(LOOKUP(1E99,SEARCH({" Bin "," Mohd "," Md "," binti "}," "&B2&" "),{"Male","Male","Male","Female"}),"")
or, simply:
EDIT:=IF(ISNUMBER(SEARCH("binti",B1)),"Female",IF(OR(ISNUMBER(SEARCH({"bin","md","mohd"},B1))),"Male","-"))
400
Last edited by icestationzbra; 12-31-2012 at 03:22 PM.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Have a look for your updated file and discover that before Mohd and Md there are no space? and next to "bin" or "binti" have spaces? Is it general?
If so:
=IF(OR(ISNUMBER(SEARCH(" bin ",B2)),ISNUMBER(SEARCH("Mohd ",B2)),ISNUMBER(SEARCH("Md ",B2))),"Male",IF(ISNUMBER(SEARCH(" binti ",B2)),"Female",""))
Thank you for the solutions..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks