Comments on: Excel IF statement for partial text match (wildcard)

Trying to build an IF statement with wildcard text, but it fails every time? The problem is not in your formula but in the function itself - Excel IF does not support wildcard characters. However, there is a way to get it to work for partial text match, and this tutorial will teach you how. Continue reading →

Table of contents

Comments page 2. Total comments: 92

  1. Hi, I wonder if you can help me?

    I need a formula which will count strings in cells whereby if a particular value appears multiple times within a particular string it will count each occurrence within one cell and for the full row.

    Thanks

    1. Hi!
      Using the MID function, determine how many times the text from cell D1 is found in the text of cell A1.

      =SUM(--(MID(A1,ROW(A1:A100),LEN(D1))=D1))

      Hope this is what you need.

      1. Hi, thanks for your speedy response!

        Unfortunately this returned a '0' rather than the intended '11'

        Perhaps I should have been more clear in the first instance

        My data is stored on a second tab within the same workbook, titled 'Data Input', the column of the data which I need the formula to count is titled 'Colour Used?'

        The formula is present on another tab called 'Analysis' and the word 'blue' in in cell C40

        The data appears as follows, as a string of text within one cell, in column 'U';

        Colour Used?
        red, yellow, blue, green
        red, blue, green
        yellow, blue, green
        blue, yellow, blue, red, green
        green, red, blue
        blue, green, yellow
        red, blue, green, yellow
        yellow
        blue, red, yellow, green
        yellow, blue, green
        pink, yellow, blue, red

        I need the formula to count each occurrence of the word 'blue' when it appears more than once within a particular cell, as it does in the 4th cell down, above, total should be '11'.

        I have tried the following formula which retuned '0'

        =SUM(--(MID('Data Input'!U2,ROW(Table1[Colour Used?]),LEN(C40))=C40))

        Any further advice would be greatly appreciated

        Many thanks

          1. Hi Alexander,

            No not that one either, i need to count recurrences of partial text within a cell and a column where that text may appear multiple times,

            Currently i can only get it to count the cells whereby it returns and inaccurate value as it is counting the cells rather than the occurrences of text.

            Is this possible?

            Thanks

  2. Hello, I already read the whole page, and I am amazed of your expertise.

    I want to ask you for opinion, as I am struggling with the following requirement.

    I need to search for a few substrings at once in the proper column, and based on that to make decision what to replace on those fields as whole value, not replace just the substring. So I need to check which of the x substrings the value belongs to, and based on that, to be replaced in whole column at once.

    I tried these queries but they work just for the first cell,not for the whole column. I guess the issue occurs when I put the part "A2:A27".
    1.
    =IF(COUNT(SEARCH({"substring1", "substring2", "substring3"}, A2:A27)) >0, "some preset day", DATEDIF(some preset date, A2:A27, "d"))

    2.
    =IF(OR(COUNTIF(A2:A27, "*substring1*"), COUNTIF(A2:A27, "*substring2")), "some preset day", DATEDIF(some preset date, A2:A27, "d"))

  3. Hi,
    I'm struggling with matching two worksheets with names ( last name, first name). One worksheet includes names with middle name initials and the other doesn't( it's a large file).
    I'm using the formula:
    =ISNUMBER(MATCH(A1,Datasource'!$A:$A,0))
    A1 = names from worksheet 1 ( names without middle initials)
    Datasource = worksheet 2 ( names with middle initials)
    I'm getting a true/false result. Some results show "FALSE" even though they should be "TRUE" only because names in sheet 1 don't include middle names.
    How can match them partially so worksheet 1 matches worksheet 2?
    Please help! Thanks.
    Joy

    1. Hello!
      To determine partial text matches, you can use the SEARCH function.
      If I got you right, the formula below will help you with your task:

      =ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH(A1,Datasource'!A1:A100)),0))

      I hope my advice will help you solve your task.

      1. Thank you for your prompt reply.
        I tried the formula you provided, and it worked!
        Thank you! I greatly appreciate your help!

        Joy

  4. Tried many examples of the =IF(ISNUMBER(SEARCH("A", A2)), "Yes", "No") function, but none of them work.

  5. I've been trying to find a formula that searches a column of postal codes where if the another cell matches on from the column it labels the cell true.

      1. EG.
        Column A consist of A6123, B4569, C1238, D7895, E1239.
        Column B consist data 123 = V1, 456 = V2 , 789 = V3 (lets say we have 100 of these need to match with Column A)

        Criterial,
        Column A which cell partial consist of 123 or 456 or 789... will prompt as V1 or V2 or V3

        1. Hi!
          To find partial text matches, use the SEARCH function. The result of the search is converted to TRUE or FALSE using the ISNUMBER function.

          =IF(ISNUMBER(SEARCH($B$1,A1)),"V1", IF(ISNUMBER(SEARCH($B$2,A1)),"V2", IF(ISNUMBER(SEARCH($B$3,A1)),"V3","")))

          Hope this is what you need.

          1. =IF(ISNUMBER(SEARCH($B$1,A1)),"V1"...

            By any chance i could change $B$1 to refer from a table? EG: =IF(ISNUMBER(SEARCH($B$1:$B$100,A1)) "but this is not working"

  6. Hello,
    Thanks for this article. I still have a question: can we replace the *text* i'm looking for by the cell number with * to check in the whole cell if the text appears ?
    Thank you

  7. =IF(COUNTIF(AC8, "*Tue*"),"40", ""+COUNTIF(AC8, "*Wed*"),"32.5", ""+COUNTIF(AC8, "*Fri*"),"40", ""+COUNTIF(AC8, "*Sat*"),"48", ""))

    Is there another way to rewrite this as excel is not allowing this because there are too many arguments

  8. Hi there,

    I kindly need help with an If function and wild cards returning different values based on if a job title contains a certain word as below

    =IF(C2="*Manager*", 100, IF(C2="*Director*", 100, IF(C2="*Specialist*", 20, 0))).......

    Since the F function dosn't work with Wild cards how can i execute this please

    Many thanks
    Barack

    1. Hello!
      To determine the presence of a certain word in the text, instead of C2=”*Manager*” try to use SEARCH function:

      ISNUMBER(SEARCH("Manager",C2))

      If the word is found it will return TRUE

      1. Great! Will try that. thank you

  9. Can you exclude a character, such as "the second character cannot be an "S" "?

  10. Hello Ablebits, I've been learning a lot and truly benefiting from everything you guys teach here as I love manipulating data within excel. Thank you so much for sharing such useful content!

    I wondered if you could help with this one:
    I'm working with databases that are not structured. I want to be able to structure them by pulling the strings gfromm the cells (and structure them onto another sheet. eg:

    Beautiful convertible white car 2 doors.
    4 doors tough black truck
    Used gray motorcycle

    I want to be able to capture data that are expected, such as color white, gr, type (motorcycle, truck, car) and structure them by placing them into a column /rwow in a different sheet.

    Not sure if this is too complicated but If I'm able to capture at least the expected wildcards: motorcycle, car, white etc and place them in different cells would also help me tons!

    Any help is really appreciated!

    1. Hello!
      To extract color, you can use the formula

      =CONCAT(IF(ISNUMBER(SEARCH({"car","truck"},B2,1)), TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",50)), SEARCH("truck", SUBSTITUTE(B2," ",REPT(" ",50)),1)-100,99)),""))

      I hope my advice will help you solve your task.

      1. Wow, thanks for your prompt reply Alexander,

        Would it be possible to unpack the formula? I try running it but it didn't work...

          1. Thanks for the reply and sorry I wasn't clear, Alexander.
            I placed your formula on the B1, and the text "Beautiful convertible white car 2 doors." I placed on cel B2. The error is #VALUE.

            The is the scenario:

            A1 - Beautiful convertible white car 2 doors.
            A2 - 4 doors tough black truck
            A3 - Used gray motorcycle

            I'd like to be able to format it like:

            Car white convertible
            truck black 4 doors
            motorcycle gray -

            The parameters in the column A will be expected such as car, truck, motorcycle, bike, plane, just like the column B and C.

            I appreciate your attention and help provided so far,

            1. Hi!
              The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
              The Formula

              =CONCAT(IF(ISNUMBER(SEARCH({"car","truck"},B2,1)),TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",50)),SEARCH({"car","truck"},SUBSTITUTE(B2," ",REPT(" ",50)),1)-100,99)),""))

              extracts the word that comes before the parameter "car" or "truck".
              If you immediately described what you want to receive, I would answer that this is impossible and we would not waste time.

              1. Hello Alexander, thank the reply and sorry for wasting your time.
                Unfortunately, the formula hasn't worked. I've got the same #Value message.

                Regardless, I appreciate your patience and effort in trying to help...

  11. Great article! I'm wondering if you can expand on the options of the =if(or(countif......

    I want different values if true or false. example

    =IF(OR(COUNTIF(A2, "*apple*"), "fruit", ""),(COUNTIF(A2, "*lettuce*"), "Veggie", ""),COUNTIF(A2, "*potatoe*"), "root", "") etc

    Is this possible?

    Thanks!

  12. Hi thanks for these tutorials
    I just wonder instead of marking yes or no I want the opposite cells to be marked as check mark ☑ or X mark but it won't work could it be possible? Thanks though ☺

    1. Hi Cheryl,

      For a check mark, you can use CHAR(252) or CHAR(254)
      For a cross mark, CHAR(251) or CHAR(253)

      Given the above, this formula:

      =IF(COUNTIF(A2, "*a*"), "yes", "no")

      can be transformed into this one:

      =IF(COUNTIF(A2, "*a*"), CHAR(252), CHAR(251))

      Note! For the check symbol and cross mark to display correctly, the Wingdings font should be set for the formula cells.

      If you are curious to learn more, you may find this tutorial helpful: 6 easy ways to insert a tick symbol and cross mark in Excel

  13. This is a wonderful guide, very detail and well written. Thanks so much!

  14. Great solution. Had to hunt around for this!!

  15. This is a wonderful guide. I am looking for optional responses. Is this possible?

    If cell H2 contains "*>AmendCreateTerminate<*" then enter "Terminate" in this cell

    1. Hello
      An Excel formula can change the value only in the cell that it is pasted in. If you need to change the content on the cell that has some value in it, you’ll need to use a VBA macro.

  16. How to compare 5 different partial texts each having a specific text value to be entered in my spread sheet. Example: Part text a = text aa
    Part text b = text bb.
    Part text c = text cc. And so on where text aa, bb, and cc are entered into my cell.

    Thank you for your help.
    Russ

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)