Comments on: Excel FIND and SEARCH functions with formula examples

The tutorial explains the syntax of the Excel FIND and SEARCH functions and provides formula examples of advanced non-trivial uses. Continue reading →

Comments page 3. Total comments: 271

  1. I AM TRYING TO FIND IF A CHARACTER IS IN A STRING AND RETURN A TRUE OR FALSE. IF THE STRING CONTAINS * THE SEARCH WOULD RETURN TRUE. FOR EXAMPLE:
    PREMARIN 0.625 MG TAB 00046110281 TAB 90.0000 FALSE
    SYNTHROID* 100 MCG TAB 00074662490 TAB 843.0000 TRUE

    1. Hello Vicki!
      Please try the following formula:

      =IF(SEARCH("~*",A1,1),TRUE,FALSE)

      Hope this is what you need.

  2. Hello Sir
    Thanks a lot for your great work. I have a query if you kindly help.
    I have list of products which has very long name, I want to split them either by first , or - or : if found none of them then will return back original product name.
    example of product name:
    1.PLUSINNO Telescopic Fishing Rod and Reel Combos Full Kit, Spinning Fishing Gear Organizer
    2.KastKing Sharky III Fishing Reel - New Spinning Reel - Carbon Fiber 39.5 LBs Max Drag
    3.Rippin Lips Super Cat Casting Rod with Glow Tip:7-Feet 6-Inch, Medium-Heavy
    4.ABU GARCIA AMBASSADEUR C3 ROUND REEL

    expected result:
    1.PLUSINNO Telescopic Fishing Rod and Reel Combos Full Kit
    2.KastKing Sharky III Fishing Reel
    3.Rippin Lips Super Cat Casting Rod with Glow Tip
    4.ABU GARCIA AMBASSADEUR C3 ROUND REEL
    Thank You.

    1. Hello Mizanur!
      If I understand your task correctly, please try the following formula:

      =LEFT(A1,MIN(IFERROR(FIND({",",";","–",":"}, A1,1),LEN(A1)))-1)

      We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Tools - Extract Text and Remove Characters.
      This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

      I hope it’ll be helpful.

  3. (585*205)*1*0.082
    sir i want to extract 585 in separate column and 205 in separate column and 1 in separate column and also digit numbers in all cases increase or decrease like 5850 or 58 and 20 or 2050 and 10 or 100so pelase help me for find forumale with mid function or any other

    1. Hello Sonu!
      If I understand your task correctly, to extract the first digit from your expression, use the formula

      =LEFT(SUBSTITUTE( SUBSTITUTE(A1,"(",""),")",""), FIND("*", SUBSTITUTE( SUBSTITUTE(A1,"(",""),")",""))-1)

      To extract the second digit -

      =MID(E1,FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""))+1, FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""), (FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),1)+1)) - FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""))-1)

      To extract the third digit -

      =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","@",2), FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","@",2))+1, FIND("$",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","$",3))- FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","@",2))-1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  4. Good morning,
    I have a situation where I have a daily update on names that all need to have the company name filled in in the next column. As many names occur more than once, I have created a source list with names and companies and want to create an IF(FIND) function to automatically populate the company names for names for which the company is known.

    I'm struggling with the second part of my formula. This is what I have so far:
    =IF(FIND(F420;'Company Source'!$A$2:$A$1793);;G420) - whereas F420 is a name, and $A$2:$A$1793 is the list of names and G420 is where the company name needs to be. Once that name is found, how do I match the company name in the source list to where I need it to be?

    I hope this makes sense.
    Thank you for your help.
    Geert

    1. Hello Geert!
      If you want to automatically enter a company name in a cell from a certain list, I recommend using an Excel drop-down list in this case. For more details on how to create it, please read here.
      However, you’re saying that you enter a name in F420, and the company name is inserted into G420. In this case you’d better use the VLOOKUP function:

      =VLOOKUP(F420,’Company Source’!$A$2:$A$1793),1,0)

      If you have additional information about companies which is located, for example, in the cell ’Company Source’!$B$2:$B$1793, you can automatically pull it and insert in cell G421 using this formula:

      =VLOOKUP(F420,’Company Source’!$A$2:$B$1793),2,0)

      Please find more details on how to use VLOOKUP here.
      If you have any other questions, I’ll be happy to answer them.

  5. How can I use the function with OR to find "ORDER" as well? at this time it only on the proper Alphabet

    =IFERROR(IF(FIND("Order",C82),"023"),"000")

    1. Hello Navdeep!
      To make the results of your search in text values case insensitive, please use the SEARCH function instead of the FIND one:

      =IFERROR(IF(SEARCH("Order",C82),"023"),"000")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  6. Hi,
    I am trying to extract only the amount from the following but facing some issues.
    Spot Award (INR 5,000)
    I only want the amount of 5000 to be removed and reflect separately in a column, without the INR. The original column should just reflect Spot Award. Could you help with this?

    1. Hello Kavya!
      You can extract the number from the text using this formula:

      =SUBSTITUTE(MID(A1, FIND("INR",A1,1)+4, LEN(A1) - FIND("INR",A1,1)-4),",","")

      The formula result will also be text.
      If it is necessary to convert it to number, please insert two “minus” signs before the first function name in the formula:

      =--SUBSTITUTE(MID(A1, FIND("INR",A1,1)+4, LEN(A1) - FIND("INR",A1,1)-4),",","")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  7. 1,83,5,White,Barry,Solihull & S H,10:00.14,,10:00.14,,,13:48:19.69,,,,10:00.14,10:00.14,,
    What formulas do I need to put in to put every other bit into its own cell?
    I have formulas to put the first 3 in i.e 1 and 83 and 5 go into their own cell. How can i get the rest to do it with a formula?

    1. Guessing you got an answer by now, but I suggest using the Text to Columns' function instead of a formula

  8. Hi forum,
    [{"id"=>2445045452, "wid"=>"XI53FEA", "order_item_id"=>"11803289568455100", "order_item_unit_id"=>nil, "quantity"=>1, "shipment_id"=>1709774212, "status"=>"CONFIRMED", "external_order_id"=>"OD118032895684551000", "customer_return_reason"=>nil}] in this data how i will extract/lookup the order_item_id in excel. kindly do the needful.

    1. Assuming your input is stored in cell 'A1'; the formula stated below will help you extracting the desired information.

      =MID(A1,FIND("order_item_id",A1)+LEN("order_item_id")+4,(FIND(", ",A1,FIND("order_item_id",A1)+LEN("order_item_id")+4))-(FIND("order_item_id",A1)+LEN("order_item_id")+4)-1)

  9. Hi Forum
    I have a text cell which can contain more values.I am looking for a formula that can determine if for instance the text contans the Word "RED", if so the formula result should state "RED", at the same time I need to search for the word "GREEN" and the result should state "GREEN". IF neither "RED" og "GREEN" is in the text cell it should give the result "GREY". Neither RED og GREEN can be in the text cell at the same time.
    How do I do that I have tried with search and IF sentences but it will fails when I get to the second search in the formula.

    1. I think this should work.
      =IF(ISERROR(SEARCH("red",A1)),IF(ISERROR(SEARCH("green",A1)),"GREY","GREEN"),"RED")

      1. Hi there, this is really helpful thank you. How would I add a this search in to this?For example if I also wanted to search for BLUE? Thank you!

  10. Hi Guys, my questions is straight forward:)
    I have a column that has lots of sets of numbers but i want to extract only numbers that starts with 7 from left.

    Thanks
    Abdel

    1. Filter with 7*

  11. Question? What formula could I use to search for a number value in a cell and give me the text description in the cell next to it

    Lets say
    A1= 8810 B2= Clerical

    How do I create a formula to search for the number 8810 and bring up the Clerical description?

  12. In my target string, the substring i want to extract is of the form Mxx - the letter M followed by a two digit number. THe target string will only have one occurrence of such a substring. The position of it though, is not fixed. For example:
    F_8_M01_MASTER_MARKS_V15 - I am looking for M01.
    F_8_M17_PTLY_XTV_V6 - I want to extract M17.
    KB1F_MESURE_M34_FIN_V01.2 - I want to extract M34
    How can I do that?

    1. DesperatelyNeedHelp:
      Here's a formula that will search a string and return the value you enter into a cell.
      You can do it two ways I'll show you both and you can decide which one works best for you.
      OK, you can enter the search value directly into the formula like this;
      Where F_8_M17_PTLY_XTV_V6 is in B13 enter this formula into an empty cell.
      =IF(B13="","",IF(ISNUMBER(SEARCH("M17",B13)),"M17"))
      Alternatively you can enter the search value into a cell and reference that cell in the formula like this:
      =IF(B13="","",IF(ISNUMBER(SEARCH(D2,B13)),D2))
      Where F_8_M17_PTLY_XTV_V6 is in B13 and you enter M17 into D2 this will return M17 into the cell where this formula is.

  13. i want to find a text 'P' in a string which is then followed by 3 numbers. The 3 numbers can be anything.

    1. If I understand your question this formula should work.
      Where "P123" is in A44 enter this in an empty cell:
      =MID(A44,FIND("P",A44),1)

  14. col A contains some set of strings, Col B contains some Sub-strings , and the corresponding coloumn C should return values of string which contains the particular substring.
    what formula can be used to pull out the string which contains this particular sub-string

    Col A Col B col C
    ABCD-alpha rain rainrain-rhyme
    1234-num abc ABCD-alpha
    rainrain-rhyme numb 1234-num

    1. Lavanya:
      Here's a formula that works if there are no duplicates,
      =IFERROR(INDEX($A$1:$A$5,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B1,$A$1:$A$5)),,),0)),"No Match")
      Just copy this down the C column and it will return the string from the A column that contains the sub-string found in the B column.
      You can expand the $A$1:$A$5 range. The B1 will change as you copy it down the C column.
      FYI, the "numb" in your sample will return the "No Match".

  15. Hello,

    I am trying to figure out how to do the following:
    Count the number of cells which have the character + within the word.
    Each of over 60 columns will have a different word.

    E.g. BOB+CAT, DOG+BIRD, CAN+, etc.

    1. Count Number of Cells That Contain Specific Character

      =COUNTIF(A23:A29,"*+*")

      Where A23:A29 is the range of cells you want to count the number of times the “+” character is in the text.

  16. Hi Svetlana, I am trying to use Find or Search function for multiple conditions, how can I use this?

    I have a table where I want to extract the first name from full name but the values are not consistent. I am getting different values in different cells for example:

    Full Name:

    Derek Gorgi
    Jonathan, Kelly

    Now I am trying to extract the first name before " " and/or before "," then how can I use Find or Search function to get the First Name without Space or comma

    To get the First value where I was getting only space, I was using the following formula as shown below but not sure how can I inculcate multiple search criteria as mentioned above.

    =LEFT(A2, FIND(" ",A2,1)-1)

    Please help.

    Thanks
    Sandeep

    1. hello sandeep sir..
      according to me..
      no need to apply (-1) in the formula =LEFT(A2, FIND(" ",A2,1)-1)
      formula is:
      =LEFT(A2,FIND(" ",A2,1))

  17. SEARCH is not case sensitive. Please correct.

    1. Hello,

      The article says that FIND is case-sensitive and SEARCH is case-insensitive. That's exactly what you say :)

  18. Hello. This page is great. Thank you for sharing. Question, Your number 4, Find text between parentheses.... How do I apply this to multiple to an excel sheet that has many multiple lines of data? Each Line will have information in parentheses and we would like to pull the information within the parentheses from each line? Would I need to apply the formula =MID(D1,SEARCH("(",D1),SEARCH(")",D1)-SEARCH("(",D1)+1) to each line of data or is there an easy way to drag and select multiple line? (I know this formula includes the parentheses, which is what we want). Thanks!

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  19. Hi,

    Great set of examples on how to go about finding stuff in excel cells. I have a question though which i haven't found in your examples (or overlooked). I get a data dump with a json string in a cell. In json there is a lot of use of the " character. I can't seem to figure out how to look for a " in a cell.

    Placing it in '"' (single quotes surrounding the ") didn't help either. Is this at all possible and if so how?

    1. Hi Remi,
      Please try to use one of the following formulas:
      1. =FIND("""",A1)
      2. =FIND(CHAR(34),A1)
      Hope this will work for you.

  20. Hi,
    I have a column with multi-word text strings, I'd like to look in that text and for any cell in the column that contains one of three (e.g.) words, I'd like to return "X", else "Y".
    For example, if the text contains 'green', 'blue' or 'purple' I'd like the result to be 'cool' else 'warm'

    COL A COL B (result)
    light pink warm
    forest green cool
    ocean blue cool
    burnt orange warm

    thanks,

    1. sorry...didn't know spaces wouldn't be retained.

      Col A | Col B(result)
      light pink | warm
      forest green | cool
      ocean blue | cool
      burnt orange | warm

      1. Nevermind...unless there is an easier solution.
        This works:

        =IFERROR(IF(FIND("GREEN",$BJ34),"Cool","Warm"),IFERROR(IF(FIND("BLUE",$BJ34),"Cool","Warm"),IFERROR(IF(FIND("PURPLE",$BJ34),"Cool","Warm"),"Warm")))

        I suppose 'Search' instead of 'Find' is optional if there is concern about being case-sensitive.

  21. I am trying to find a way to search a specific combination of letters in a list of words. For example, lets say I have a list of 100 words and I want to find all of the words that have exactly 1 A and 1 U in them and they don't have to be consecutive. Mutate, jaunt, magnum are examples that would meet my criterion. Does anyone know how to do this? Thanks.

    Also, if possible I would like to be able "extract" (if that is the right term) those letters in alphabetical order into the cell to the right of the word. Eventually, I will sort the list of words and the extracted letters by the extracted letters to group the words that have the A and U.

    1. Hello,

      I believe you could use our Advanced Find & Replace add-in. On the first step you choose all your 100 rows and set the letter "A" as a search criterion. Then, you can:
      1. either select all the found rows/columns/entries and run the new search over the selection with the letter "U" as a criterion OR
      2. export the found entries to another workbook and run the search for the letter "u".

      You can learn how the add-in works on its help page.

      As for the second part of your task, I'm afraid you will need to use some kind of the VBA, but for that please ask around Mr. Excel forum.
      Hope this helps!

  22. OK, I'm stuck.... somebody please help me out.

    I have over 110 unique 7 digit values in Column B (50144815) and I need to see if/where in my spreadsheet they exist (in a different row in Column C).

    Column C is 450 rows long and the unique 7 digit numbers would be hidden in a test string as exampled below:
    "Retracement Survey of Alba, PID 50144815, Plan# 89584, unsigned digital copy, unsigned paper copy 53"

    Vlookup works when it's only numbers comparing numbers, but when there is text and numbers together is seems to be choking up and not returning results.

    1. Hello, Wesside,

      you can try using VLOOKUP with wildcard characters (asterisk in particular). It enables searching for any sequence of characters within the cell.
      Please take a look at these examples to learn how to level your formula up.

      Another variant would be to use conditional formatting to highlight the row with the occurred values. And we have just a perfect tutorial for that either :)

      Hope this info will be helpful!

      1. Ah, still having some trouble with this...

        the * * trick doesn't seem to work using my limited knowledge of excel lol.

        basically I want to compare column B (108 rows) to see where those number are in Column C (450 rows).

        I had a "bit" of success but not much.

        Then I tried this formula =vlookup('[Escrow108.xlsx]CBO Tab#2 - Other DNR'!$B$2:$B$109,O:O,1,FALSE)

        I tried taking Column B and putting into another work sheet, then VLookup to compare column B on sheet 1 with column C on sheet 2, nope, even less success. The formula I used seems to stop searching at row 109 in column c...PLUS it isn't finding numbers in column c that I know exist in column b.

        ASLO, i should note that in some cases, 3 rows of Column B might be listed in the text for 1 row of column C.

      2. Fabulous! I'll give it a try, was currently trying variations of "index and Match" functions

  23. I am trying to figure out how to remove the last few characters of text strings in a table that are not all the same length and last 3 characters are different.

    WM08-04358*A 1 to display as WM08-04358
    CLX-87390*B 3 to display as CLX-87390

    Any suggestions are appreciated!

  24. Hi Expert,
    I have a problem using Search if there is same character on the beginning.
    Here is my problem.
    on column A i have this data:
    A2=> Green;Apple
    A3=> App;technology
    A4=> Java; API

    How can i get row that contains text "App"?

    Thank you for your help

    1. Hi there.... Just filter the column with contains as text.. Input text "app".

      You will get all rows where appword is found

  25. Ohio 44707 USA
    Alabama 36609 USA
    North Carolina 27591 USA

    I want to extract data before any first number character. For ex.

    In first example, I want to extract OHIO
    In second example, I want to extract Alabama
    In third example, I want to extract North Carolina.

    Please anyone can help?

    1. Hi Anand,

      Not sure if you still need an answer but this will do what you require:
      =TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

      Change A1 as appropriate

  26. Hi,

    I just want to ask, I need to search how many transaction was made in an hour, can you help me please, ex. hour 10:00:01 AM to 10:59:00 AM. I need to get how many transaction was made between the said hour

    Thanks

    1. Easy... pivot tables

  27. Hi, with regards to the extracting a value between parentheses ().. if a cell doesn't have these a #VALUE! appears. How do I remove this? i.e with an IF "" included? Formula I have is as follows:
    =MID(C5,FIND("(",C5)+1,FIND(")",C5)-FIND("(",C5)-1)
    Thanks in advance

    1. Try embedding your MID function inside of an IF(ISERROR()) function.

      e.g.,
      =IF(ISERROR(MID(C5,FIND("(",C5)+1,FIND(")",C5)-FIND("(",C5)-1)),"",MID(C5,FIND("(",C5)+1,FIND(")",C5)-FIND("(",C5)-1))

      1. Paul: use IFERROR... it's cleaner.

        =IFERROR(MID(C5,FIND("(",C5)+1,FIND(")",C5)-FIND("(",C5)-1),"")

  28. Supose i have this sentance -- "Can verified list be used from an earlier project of another client targeting same technology?",

    and i want any alphabate count. then what will be the easy formullas?

  29. I want to check whether an exact number exist in an array of numbers. For example, I have a list of numbers from A1.C10. I want to check whether a number 10.5 is in this list. If it is, the result can be True or False, if it isn't.

    1. Hi,
      Try below
      =SUMPRODUCT(COUNTIF(10.5,A1:C10)=1

      1. =IF(COUNTIF(A1:C10,"10.5")>0,"True","False")

        @Anthony, This formula will help you incase if you were looking for solution

  30. Hi Svetlana,

    I have few cells which had data like "adasd **red** asdfas"
    I need to search all the cells which contains "**red**" string in it.

    Could you please help.

    Thanks

    1. Create a helper column and try below
      =ISNUMBER(SEARCH("**red**",Cell_with_text)

      1. greate

  31. =IF(COUNT(SEARCH("ab",W25)),"1",IF(COUNT(SEARCH("cd",W25)),"2",IF(COUNT(SEARCH("ef",W25)),"3","0")))
    from above formula result is fine, but if a cell(W25) contains both "ab" and "cd" it gives result as "1", where i required is "2". pls help how to search exact content.

    1. Try =IFS(
      COUNT(SEARCH("ef",W25))>1, "3",
      COUNT(SEARCH("cd",W25))>1,"2",
      COUNT(SEARCH("ab",W25))>1, "1" )

  32. Hi,
    Thanks for posting this. It's awesome.

    How do I extract the middle text "Regional1" Company1 and material1 for the example below? The text strings are not always the same. I tried combining the find but I just couldn't get it to work.

    12345-LAC-600-Regional1-Company1-Material1-US

    Thanks

    1. From you question I am not sure what you are attempting to accomplish but I will post a solution assuming that the phrase is located in B12 and you want "Regional1-Company1-Material1" located in another cell. The solution works as long as the number of dashes before and in the extracted information does not change. The number of characters before or after the dashes is irrelevant.

      =MID(B12,SEARCH("^",SUBSTITUTE(B12,"-","^",3))+1,SEARCH("^",SUBSTITUTE(B12,"-","^",6))-1-SEARCH("^",SUBSTITUTE(B12,"-","^",3)))

      The first SEARCH has a nested SUBSTITUTE that ends up locating the third dash and the +1 tells MID to begin on the next character after the third dash. The second SEARCH has a nested SUBSTITUTE that ends up locating the sixth dash in the string and the -1 locates the character before the dash and then the final SEARCH is the same as the first but is subtracted from the second SEARCH to tell MID the number of characters situated between the third and sixth dashes.

      If you were actually attempting to place "Regional1", "Company1" and "Material1" in separate cells you would simply manipulate the formula in those separate cells to find the third and fourth dash, fourth and fifth, and fifth and sixth dash. NOTE: The last number in each SUBSTITUTE would be the only thing that changes.

  33. Hi there !!!

    It would be much appreciated if anyone could help me to solve my problem:
    I have sheets more than 500 in a workbook. My sheet names are 1,2,3,4,5 till 500. In each sheets I have a formula in a cell. Example : VLOOKUP(A1,A1:AL300,1,FALSE).
    My request is that would it be possible to change A1 to A2 in sheet 2, A3 in sheet 3, A4 in sheet 4, till Sheet 500 automatically?
    It is very difficult to change manually for this 500 sheets.
    Looking forward to hearing from you soon.
    Kind regards

    Alex

    1. Yes it is possible through advance excel (macro)

    2. ya it is possible

  34. IF A2's value = O and B2's value = Y then take date from C2 and IF A2's value = O and B2's value = N then take date from D2 ELSE TAKE DATE "0"

    1. Hi AJAY,

      you can use the following nested IF functions:

      =IF(AND(A2="o",B2="y"),C2, IF(AND(A2="o",B2="n"), D2, 0))

  35. Hi Svetlana, your solutions, excel formulas... are really really great. I am new in my field "MIS" and I just found these sites, now I am learning a lot from the links and the formulas and the new things you provide. Thank you very much. Appreciated.

    1. AFAIC tha'ts the best answer so far!

    2. Hi Suliman,

      Thank you for your feedback! I am really pleased to know that you have found our tutorials helpful.

      1. Hi,

        I have a problem in excel for find and pest,

        The problem is "in a excel one page i have some numbers(123456), same page same number included in some words(abcd123456), so i can find out based on any formula please let me know, i am waiting.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)