+ Reply to Thread
Results 1 to 11 of 11

How to match cell with three text criteria?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    How to match cell with three text criteria?

    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..
    Attached Files Attached Files
    Last edited by jewellove; 12-26-2012 at 01:37 AM. Reason: edit

  2. #2
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: How to match cell with three text criteria?

    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.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,504

    Re: How to match cell with three text criteria?

    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

  4. #4
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: How to match cell with three text criteria?

    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"; "")

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: How to match cell with three text criteria?

    In C1 cell
    Formula: copy to clipboard
    =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

  6. #6
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    Arrow 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
    Attached Files Attached Files

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,318

    Re: How to match cell with three text criteria?

    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

  8. #8
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: How to match cell with three text criteria?

    Try this:

    =IFERROR(LOOKUP(1E99,SEARCH({" Bin "," Mohd "," Md "," binti "}," "&B2&" "),{"Male","Male","Male","Female"}),"")

  9. #9
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: How to match cell with three text criteria?

    or, simply:

    =IF(ISNUMBER(SEARCH("binti",B1)),"Female",IF(OR(ISNUMBER(SEARCH({"bin","md","mohd"},B1))),"Male","-"))
    EDIT:

    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 -

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,504

    Re: How to match cell with three text criteria?

    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",""))

  11. #11
    Forum Contributor
    Join Date
    10-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: How to match cell with three text criteria?

    Thank you for the solutions..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1