In this video we use the IF function of Excel to test if a cell contains specific text.

Usually with the IF function you test a cell for a complete match, but in this example we need a partial match on text.

We then extend the scenario to handle a problem and then test for two different specific text strings.

Find more great free tutorials at;

*** Online Excel Courses ***

The Ultimate Excel Course – Learn Everything ►
Excel VBA for Beginners ►
Advanced Excel Tricks ►
Excel Formulas Made Easy ►
Creating Sports League Tables and Tournaments in Excel ►

Connect with us!

Google+ ►
Facebook ►
Twitter ►


Xem thêm bài viết:

22 thoughts on “Excel IF Function: If Cell Contains Specific Text – Partial Match IF Formula”

  • Hi Alan,
    Thank you for the tutorial.
    I am trying to achieve in the formula to show the following:
    If one "CB2" is standalone to show "local", if "CB2 & "CB3" (combined) to show "local & far" and if "CB3" to show "far"
    How can I achieve this?

  • Ole Thomas Lindebø says:

    I'm sorry, but I can't seem to get the formula to function. Any tips? 🙂
    Collum A2 has the text "abc" in it.

    =if(ISNUMBER(SEARCH("abc" ,A2))"yes","no")

  • What is i want

    A1 is Paul Levine
    B1 is Paul Levine Andrew

    C1 is the formula to use to show TRUE as both are Paul Levine but additional text does not matter

    How do i do this?

  • Gerald Robinson says:

    Would there be a formula to identify the postcode and copy it to the next column for each address? ie] cell 2B would have CB5, cell 3B would have CB3, etc?

  • I want to know how to search the data in a different range of columns with if function? For example if A1 = any one cell in the range from d1:d5 then if true “Yes” if false “No”. Is this possible?

  • William Abraham says:

    I am having an error, only for first few line items this formula is getting applied, I have a list of text/numbers if a cell contains any of these I want to see the specific text/numbers from the list in another cell, can anyone help

  • Commanderfury44 says:

    How would you write a formula to look for specific text (lets say in this case "Hello"), take a numbers in another row that is with "Hello" and get an average of all of the "Hello" numbers together.

  • how to check if the cell has English letters? I have people register from many country and want to know how many people are speaking English.

  • I've been trying to do the "if" function with a drop-down box but I can't get it to work. The ddrop down has the "yes" (with 5 points), "no" (0 points and "NA". I can't get it to work. Can that be done?

  • filipe coutinho says:

    Thanks a lot very useful. Can u help on this please. I have to enter (345) but sometimes I enter without () eg 345. Then I have to find which entries are negative number and correct.

  • AlbarS Timika says:

    Dear Alan,
    Please help me, How to Combine these Formulas:
    1. IF(OR(OR(ISNUMBER(SEARCH({"*Ports*","*Port S*","*Amama*","*Bagh*","*Post*","*Coal*"},$E3))),"PORTSITE","Recheck")

    2. IF(OR(OR(ISNUMBER(SEARCH({"*Cargo*","*CD*","*Steved*"},$E3))),"CARGODOCK","Recheck")

    3. IF(OR(OR(ISNUMBER(SEARCH({"*28*","*Avco*","*Core*","*Base*"},$E3))),"MP28-BC-BANDARA","Recheck)

    4. IF(OR(OR(ISNUMBER(SEARCH({"*31*","*32*","*LIP*","*Kuala*","*Yard*",},$E3))),"MP32-LIP","Recheck),"OTHER-Need to Remakesure" (Other then as OTHER-Need to remake sure)

    Need to combine or shorten in a Cell..please…
    For each Formula can function if separated, but I'm confused to combine it as a formula Excel just in a Cell.

  • Sudhanshu Mohanty says:

    Is there a way to return the text that you are searching for in the cell? So for the example above it is looking for CB2. I have a list of search items and a range of text strings. I want to search for these items in the text strings – if it finds the value then output that value that it finds.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>