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

  1. =LEFT(A1,FIND("*",A1))

  2. Ma'am Lets say i have (3242*4643) and i have to extract from ( to * then what would i have to do?

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

  4. Wonderful.
    Thank you

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

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

  7. In one tab ('Orchard details')of a spreadsheet I have a list of our 14 orchards and their respective varieties in each orchard. Some orchards have more than one variety. Some orchards have the same variety as other orchards. One field in the list is fallow at present. This list is in cells AA6:AA27. In another tab ('Orchard varieties'), I want to create just a list of the varieties that we grow taken from the 'Orchard details' list, reporting the (orchard varieties' tab in cells B93:B105. As we grub old orchards and plant new ones we may change the variety so the 'Orchard varieties' list would need to change as we replant our orchardsand record them in the 'Orchard details' list. Thank you.

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

  9. Hi,

    I'm using the below formula to extract data (equipment number) with a hyphen. For example, cell contains:

    V-1770A BLAST/PAINT EXTERNAL SURFACE (#17337C)

    the formula:

    =TRIM(MID(SUBSTITUTE(E3080," ",REPT(" ",99)),MAX(1,FIND("V-",SUBSTITUTE(E3080," ",REPT(" ",99)))-50),99))

    returns the results:

    V-1770A

    The formula works great, but I have to change the FIND find_text parameter for each line.

    Examples of various data lines:

    I-1602 A/B "DRYER" DEMO SCAFFOLD # 2080 (#17324A)
    E-1403 "IN/OUTLET VALVE " DEMO SCAFFOLD #2076 (#17324A)
    C-1407 "INSPECTION WINDOWS" REPLACE METAL/SEALANT (#17324B)
    T-1311C "ROOF TOP NOZZLES" CLEAN/PAINT (#17324C)
    V-1770A BLAST/PAINT EXTERNAL SURFACE (#17337C)

    No matter where the information is on the line the formula works, but I do not want to have to edit the formula each time the equipment letter changes.

    I've tried using a Define Name table in the find_text parameter, but that does not work. Returns #VALUE.

    Any suggestions or help would be greatly appreciated.

  10. 6601280012088
    How do I extract the 7th number. =mid(C7,7,1)
    if greater/equal to 7 - indicate "Male" otherwise "Female"

  11. Hi,

    I am trying to find a formula to yield the word to the left of the first space at the right. For example in the first item below, I would want the formula to result in BRAZR. The reason I cant work from the left (or at least I cant seem to) is there is a different number of spaces to get to the word to the left of the last space on the right as you can see below with the other examples. Could you please help me out with this??

    XXX 5C-15 HZ BRAZR 3-14-47-14
    XXX KARR 13-28-65-3
    XXX HZ LEDUC-WB 16-9-49-25
    XXX HZ BANTRY 4-21-19-13

    Much appreciated.

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

  13. POD CONTAINER SIZE TOTAL 40HQ reqirued result
    KRPUS 40HQ x 1; 40GP x 10 ; =MID(B5,SEARCH("HQ",B5)+1,(SEARCH(";",A2)-SEARCH("HQ",B5))-1) 1
    PKKHI 20GP x 1 ; 40HQ x 100 ; 40GP x 1 ; 100
    USNYC 20GP x 1 ; 40HQ x 12 ; 40GP x 200 ; 12

    Kindly assit me to correct above code

  14. Hi How to use ISnumber and search formula to find couple of words from one text. Ex I am trying to find either "SHAW or "SHW" from this particular text "GL123456-Defee SR Heloc FR PCI LAND-SHAW"? I am using the formuls Is number(Search"SHAW",text")) and it returns with True or false result which is fine. But I want to embed word"SHW" too in the formula so I can find if the account is SHAW or SHW. How can I do that? I hope I am making sense and able to explain it.
    Appreciate the help.
    Thanks!!

  15. Hi,can you help me,right function in how using "len" function

  16. Hi, can you help me to find out "either" "or" condition in excel. also how to find probability from any data.

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

  18. Hi, How to get the result for Search Function by selecting the range

    Ex Search("Lucy",A1:A10)

  19. Hi,

    My data is like this:
    Cell A: location (duplicate values)
    Cell B: Revenue from those locations (positive and negative values)
    Cell C: Month value

    For a particular location (multiple values), I would like to get the value of the month when the revenue was greater than zero for the first time.

    Can someone help me with this?

    Thanks!

  20. Hi, could you advise for my below sample?
    Text sample 1: Creat highchart 4.0.418.02%91.98%-0.80%Fixed IncomeEquityOthersLoan010203
    Text sample 2: Creat highchart 4.0.49.00%92.00%EquityReal Estate04020
    I want to extract 2 text from each text sample above:
    A. Start after 4.0.4 till the last %
    B. Start from last % till before the first number show(the first number is always 0)
    That is: "18.02%91.98%-0.80%" and "Fixed IncomeEquityOthersLoan" from text sample 1
    "9.00%92.00%" and "EquityReal Estate" from text sample 2
    The difficulty is the number of % occurrences is not fixed and the length of last number is not fixed. I'm thinking if there is a way to find position of the first % starting from right? I really got stuck here. Thanks for help in advance!

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

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

  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 there.... Just filter the column with contains as text.. Input text "app".

    You will get all rows where appword is found

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

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

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

  28. Hi
    Can Search Function be used for array?

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

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

  31. 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")

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

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

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

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

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

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

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

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

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

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

  42. Maam,

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

    thank u

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

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

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

  46. 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?

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

  48. 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?

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

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

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