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 10. Total comments: 446

  1. I have this formula =IF(ISNUMBER(SEARCH(D$1,L41&M41)),A41,"") every thing was perfect but than I found that this formula is searching some words from A1 that match in L41& M41 . example I only wanted XXX YYY ZZZ but it also displaying XXX only

  2. Hi, I have a requirement where in there are 10 parameters. Each parameter would result in different complexities from Very low complexity to Very high complexity. Based on these complexities an overall complexity should be arrived at.If 2 of the parameters result in very high complexity and the remaining are other complexities then the end result would be very high complexity. Like wise for other complexities from top to low. Could you please help in writing a formula for this.

    Thanks
    Prashant

  3. 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!

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

    You will get all rows where appword is found

  5. 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

  6. 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

  7. Hi! This is EXTREMELY helpful! Thank you for all your efforts. I was wondering if it is possible to use this in a more complex situation:

    if the text string contains the word "doctor" or the word "facility", then I would like to add a category column, and have the word "Provider".

    if the string contains "drug" then the category would be "Pharmacy", and so forth.

    I was thinking of combining IF/THEN but it kinda blows my mind...

    Any thoughts?

    Thanks!

    John

  8. Hi
    Can Search Function be used for array?

  9. Hi Svetlana!
    Its really helpful post.
    I have a query, in which i have to extract multiple columns into text on daily basis.
    Kindly formulate a formula, in which each text in a column should have to be separated into separate columns.
    A2=
    B# 12/16, 09-NOV-16 , [C] , Company X [ Company Y ]
    Required result:
    | B# | 12/16 | 09-11-2016 | [C] | Company X | Company Y |

    Looking forward for guidance
    Regards

  10. Hi All

    I was hoping for some help. I need to extract the string which starts with either Z or A which is followed by 5 numerical characters; e.g.:
    jd422290_backing_A54321sheet_960x1850_x_6_0
    jf418407_Z12345_02_x9_1_0

    a) A54321
    b) Z12345

    Any help would be appreciated.

  11. Good day,

    Very new to posting stuff on these forums so please bear with me. I have an issue with a search formula.
    I am doing multiple searches in a field. Doing the first half works but when I ad to this it no longer works.
    Here is an example of the formula.

    =IFERROR(IF(F3="Crutch - Taps","Crutch Taps",IF(F3="Gate Valves","Gate Valves",IF(SEARCH("Waste",F3)>0,"Waste",IF(SEARCH("Drain Cock",F3)>0,"Drain Cocks")))),"Others")

  12. I am trying to use the find/count to pull apart a list of names that I was given.
    This is format...LastNameLastNameFirstNamePosition with no spaces.
    eg...BlowBlowJoeMgr which I want to change to Joe Blow
    I was trying to use the Count/Upper functions to achieve this. So everything from the 2nd uppercase letter, to the third uppercase letter is last name and from 3rd to 4th is first name.
    Thanks,
    Ron

  13. 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

  14. Hi, i'm making the attendance sheet and i want to showing days of Absent and weekly Off in specific cell like below format. but i can't able to do this by if and find formulas. so please help me....

    date- 01 02 03 04 05 Absent OFF
    A P A P A 01,02,05
    P O P O P 02,04

  15. Hi, how can i apply @FIND to a range of cells. pls advise.

  16. Dividing data such as
    6003-0-18-19
    6059-0-7-54
    1550-1-0-4
    seperated by "-"

  17. excel sheet,list of vehicle num bt find the one vehicle num what used to formula

  18. sorry i had not defined problem correctly,
    i had contain data as
    30\EE-III\2016-17 1st and final bill and
    50\EE_II\2015-16 5th and final bill
    like this i had 2000+ cells containing different type data ending with 3rd and final ill like this
    ,please help to delete 1st and final bill, 5th and final bill like so on...

  19. i had contain data as 30\EE-III\2016-17 1st final bill ,please help to delete 1st final bill from my data. because i had nearly thousands cells in cell

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

  21. Suggested Edit:

    "Unlike FIND, the SEARCH function is case-insensitive and it allows using the wildcard characters, as demonstrated in the following example"

    the statement that says "SEARCH function is case-sensitive" is false, the SEARCH function IS NOT case-sensitive...

  22. Maam,

    your Tutorial is very helpful. now excel easy for me only bcz of youuuuuuu

    thank u

  23. Hi,
    Could you please teach me how to I extract words before and after Bin or Bt, such as name below
    Nor lina Bt Hussan, Ahmad B Mohdmmad Rozali

    I need a fomula that can seperate first name and last name. Since I have a long list of names full of different word length before and after Bin or Bt.
    Please help me with this. Thank you for your help

  24. Hi, how to replace the following standard descriptions

    Standard Description : OSL MDF 17mm 8x4 Chocolate 18678

    Example Description : Shade: 18678 col: Chocolate OSL 8x4 board

    Regards
    D S Shivarudra

  25. I want such a formula, If I put the a string value then it will give me the whole value of that cell.

    For example: if in Cell A2 have value "merge a fund" and I want to search by only fund, fun, fu,f. Cell A2 should be highlighted

  26. Hi, need help if this can work in excel

    C2 have a phrase stated as "Old maiden character"
    C3 have a phrase stated as "Old tatoo artist"

    what I want to happen in D2 and D3 cell is to search for the word "Old" in the cell C:C, and if it is found D cell will generate the word "Old"

    is this possible?

  27. Hi Svetlana,
    I would like to use a function to do this...

    IF A2 contain the text in A1 give me 1, if A3 contain the text in A1 give me 2, ELSE give me 0.

    suggestion? The find function seems not to work as it gives #VALUE! and stops this function:

    =IF(FIND(CELL("contenut",A1),$A$2)>=1,1,(IF(FIND(CELL("contenut",A1),$A$3)>=1,2,0)))

  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 have some 3 condition are as follows

    1342435-2
    2-5446465-0
    1131486

    i need to remove the number before and after the "-"dash using formula. these all 3 condition should be met

  30. 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

  31. Hello Mrs. Svetlana...have a nice day

    Problem sample:

    Lets say I have data like this at A1 to A1000 with random code

    And I want to find and display 765, 459, 111, 989

    if i use filter it will display just one 765 or 459 or 111 or 989, or you can find them just using the scroll but that's the hardest way, i have to look one by one to find the data.

    I just want the easy way like if i type 765;59;111;989 the data will display at the table sheet all of them not one by one.

    have you found data like this? Please Share the formula.

    My, best regard.

    thank you.

  32. 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

  33. Hi wannt to take only MSA from the P.IQ.MSA.FI.02.003.F002. please let me have the formula

  34. Thank you for posting the article. I am trying to do a find and replace on a large scale. i have an english and italian translation. I would like to find all the italian phrases and replace with english. Rather than do them one at the time i have thousands of different translations. i have a file in excell with english in column A and Italian in column B Please help!

  35. =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" )

  36. i want to know month position no in particular cell

  37. 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.

  38. 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

  39. 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))

  40. 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.

  41. Awesome uses of #find & #search...

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 :)