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 2. Total comments: 268

  1. Please can you help!

    I am trying to extract value from cells range J7 & J8 e.g 160YR, 100YR, 443IN, 960K3, 472P2, 142YM (see examples of data below)

    But these strings could be anywhere in the J7 & J8 Cell longer string of text and the length of the number part before YR could vary.

    For example,

    column J7 & J8 data could be:

    example 1
    TX01 INR 160YR TX02 INR 100YR TX03 INR 443IN
    TX04 INR 960K3 TX05 INR 472P2 TX06 INR 142YM

    example 2
    TX01 INR 63IN TX02 INR 1005K3 TX03 INR 472P2
    TX04 INR 91WO TX05 INR 142YM TX06 INR 260YR

    I need a formula that will only return the number part before YR,YR,IN,K3,P2,YM until the space before the number part. I am looking for a formula that would return the results from the data in J7 & J8
    Note1: an example where the YR is populated twice, but would always be the different value.
    Note2: my YR,YR,IN,K3,P2,YM place is change as see example 1 & example 2

    Results required:
    A7 | B7
    Fixed |
    ----------|--------
    YR | 160
    YR | 100
    IN | 443
    K3 | 960
    P2 | 472
    WO |
    YM | 142

    I can’t use Left, Right or Mid to get my results and am now at a loss! Be so grateful if you can help me.

    Thanks

  2. Abdul-Aziz-khan-Mahmud-Akim-Nahid

    Mentioned the above, how to identify the 4th dash (-)?
    and
    5th dash (-)
    Thx in advance.

      1. Thanks for reply.
        But I want to take 4th dash (-) or 5th dash (-) not substitute.
        Can I get it by using MID & FIND?

          1. Thanks for reply.
            Actually, I want to take 4th dash by using MID & FIND from the above statement. Not want to replace.
            Also, I will be grateful to you if you mention how to find 4th & 5th dash by using FIND or SEARCH function.

  3. 00005280
    OCT'22 xxxxxxx
    xxxxxxx
    xxxxxxx
    xxxxxxx

    =IF(SEARCH("oct",H32),"oct",IF(SEARCH("sep",H32),"sep",IF(SEARCH("aug",H32),"aug","error")))

    the above shown is one cell with wrap text, I would like to extract the oct/sep/aug out from the cell. It's worked fine with Oct, but when turned to sep or aug doesn''t work... can someone help? thank you.

    1. Hello!
      Add an ISNUMBER function to the formula to turn the error message into FALSE.

      =IF(ISNUMBER(SEARCH("oct",H32)),"oct", IF(ISNUMBER(SEARCH("sep",H32)),"sep", IF(ISNUMBER(SEARCH("aug",H32)),"aug","error")))

      Hope this is what you need.

  4. =LEFT(A1,SEARCH(" ",A1))+LEFT(A2,SEARCH(" ",A2))+LEFT(A3,SEARCH(" ",A3))+LEFT(A4,SEARCH(" ",A4))
    i have data like this
    row 1 data = 18 ANY OUT OF 4
    row 2 data = blank
    row 3 data = 28 ANY OUT OF 4
    row 4 data = blank
    row 5 data = 9 ANY OUT OF 4

    i want to sum numbers start of the column how can i do this i apply above formula but in second row there is no data in column.

    1. Hi!
      If the formula returns an error, you can replace it with a different value using the IFERROR function.

      =IFERROR(LEFT(A1,SEARCH(" ",A1)) +LEFT(A2,SEARCH(" ",A2)) +LEFT(A3,SEARCH(" ",A3)) +LEFT(A4,SEARCH(" ",A4)),0)

  5. I need to search/find/extract the specific text from a cell and paste it in another cell. Ex: if
    Cell A1 has Apple|Mango|Orange in cell B1 I want to have word Apple by searching it in cell A1, if cell A1 has apple, the result in B1, it should show "Apple" like wise Cell A2 has Orange|Mango|Apple. in cell B2 I want to have word Mango by searching it in cell A2, if cell A2 has Mango, the result B2, it should show "Mango". Also, I want concatinate this 2 formulas in one cell. Can you please help me.

  6. Hello,
    I am trying to find data (mixture of text and numbers) that are separate by 2 spaces. In one cell there can be 1,2,3 or up to 8 pieces of data to return. E,g, I want container numbers:
    TSHB5102371 TSHB5112847 to XYZ
    I want to separate out TSHB5102371 and TSHB5112847
    The next cell have 6 strings I want to return into separate columns:
    TSHB5115980 TSHB5112071 SLZU5114773 TSHB5118803 TSHB5107650 TSHB9518
    As you can see they are not consistent in length (and they do not always start with the same 4 letters)
    Here I would want to have a formula in 1 column that returns the 1st container number, 2nd container in the next column and so on. How do you adjust the formula in each column to get to the next string?

    I am stuck at going beyond the 2nd and 3rd data points

    Thanks

  7. I am compiling yearly data on sales. it also includes new consignment, opening, and closing balances with customer names and numbers. It has text in the first column and numbers in the next column. in the new workbook, I want to add if the text is this in the entire sheet then put the value from the next cell. which formula is a good option.?

    1. Hello!
      If I understand your task correctly, to look up values, you can use the VLOOKUP function as described in this tutorial. If this is not what you wanted, please describe the problem in more detail.

  8. Hi,I would greatly appreciate it if you could let me know which formula would be appropriate for the following task.
    I want to specify all the position "A" in the sentence and the output is a list of positions.
    For example:

    TCCCTACATCAGTCTTCCAT

    there is an "A" character at position 6, 8, 11, and 19.

    1. Hello!
      Use the MID function to find the desired character in a string. Merge the results into a string using the TEXTJOIN function.

      =TEXTJOIN(",",,IF((MID(A1,ROW($A$1:$A$30),1)="A")*ROW($A$1:$A$30)>0, (MID(A1,ROW($A$1:$A$30),1)="A")*ROW($A$1:$A$30), ""))

      I hope this will help.

  9. Want to identify row wherein a particular word is available,
    Example, Identify cell wherein "spent" word is available with help of a formula
    Have tried FIND and SEARCH functions however output is the position of the start of the word i.e. for 1st Record result is 6

    # Body Expected value
    1 Have spent Rs. 770 on ICICI credit card TRUE
    2 Debited Rs. 1680 from Savings account FALSE
    3 Spent Rs. 800 on AXIS credit card TRUE
    4 Rs. 1000 has been credited to Account FALSE
    5 INR 1200 spent on ICICI Bank Card TRUE

    1. Using dash to differentiate separate fields of the table for better understanding

      #--- Body-----------------------------------------------------------Expected value
      1 -- Have spent Rs. 770 on ICICI credit card ----------TRUE
      2 -- Debited Rs. 1680 from Savings account-----------FALSE
      3 -- Spent Rs. 800 on AXIS credit card--------------------TRUE
      4 -- Rs. 1000 has been credited to Account------------FALSE
      5 -- INR 1200 spent on ICICI Bank Card------------------TRUE

      1. Hello!
        If all the text is written in one cell, then you can show the result of the search for the desired word using the ISNUMBER function. It converts the found position number to TRUE or False.

        1. Thanks Alexander for responding

          problem statement is (as per example given) finding the cell wherein word "spent" is available in the cell body text by a formula, by using ISNUMBER function i can identify value is number or not which doesnt suffice my requirement
          used FIND, however it gives me starting position of the word in the text
          FIND("spent",E3) O/P is 6
          SEARCH("spent",E3) O/P IS 6

          1. @Alexander

            Ur info helped
            used below formula
            =ISNUMBER(SEARCH("spent",E2))

            and it worked

            Thanks....

  10. Column A: I have a column that has id's , these are consistent and have 5 digits. ex: 12345
    Column B: There is another field that has text, numbers and is a long string. abcdfef12345dkjsajdkjadj
    Column C: has a another id that is required AB11111111

    Column D : for the id 12345 result should be AB11111111

    I want to lookup column A (i.e 12345), has to be looked up in column B and the result should be in Column D as shown above

  11. Hello,

    I would like a formula to return all the information in the cell which contains "[ " starting from the last in the column.

    The below formula seems to work but only with an exact search and not wild card "*"&[ &"*".

    =LOOKUP("search value"; SORT(B2:B16); SORT(B2:B16;B2:B16;TRUE))

    Can you help me?

    Like below example. It should return: 13:35 - [ SF LOYEUNG CO LTD] Willoughby College, Ru
    09:46 - [ KING SAVERS LTD (B VALLON)] Bo'Valon Mall, A
    11:13 - A Tech Group, Rue la Bourdonnais,
    12:50 - New Fashion, Rue de Suffren,
    13:35 - [ SF LOYEUNG CO LTD] Willoughby College, Ru
    15:55 - Les Copains d'abord, Rue Shivanand

    Thank you

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =XLOOKUP("*"&"["&"*",A1:A9,A1:A9, ,2,-1)

      You can learn more about XLOOKUP function in Excel in this article on our blog.

      1. Hello Alexander,

        Thank you for your quick reply. I am using google sheets and it is not recognizing Xlookup.

        Do you have a suggestion?

        Thank you in advance.

        Priya

        1. Hello Alexander,

          I tried using excel for this calculation only and it works perfectly.

          Thank you a lot.

          However, I have a little issue, several sheets in the worksheet have a few merged cells which I would like to remove at one go. Unfortunately, my find button does not have a format field where I can find all merged cells and remove them at one go. Would you have a solution for this?

          Thank you

          1. Hi!
            Unfortunately, using formulas, it is not possible to search and select cells with a specific format.

  12. Hi,

    I would greatly appreciate it if you could let me know which formula would be appropriate for the following task.
    I have a table of over 10,000 entries, all containing a random 20 character string of letters, as follows:

    ...
    TCCCTACATCAGTCTTCCAT
    CAAGATGAATAACTAATTAT
    ACTCAACAGCAAGACGACTG
    ...

    I would like to determine the position of each "A" character in each of these individual cells.

    For example

    TCCCTACATCAGTCTTCCAT

    there is an "A" character at position 6, 8, 11, and 19.

    I would greatly appreciate it if you could point me in the right direction to screen through these 10,000 entries. Alternatively, I was thinking I could create 20 extra columns for each entry, each time clipping the first character of the 20 character string, and then determining the first occurrence of the "A" character each time, but this seems really inelegant.

    Your help would be very much appreciated.

    Thank you so much for your detailed article.

    1. Hello!
      To get a list of positions in the text of the desired character, try this formula:

      =TEXTJOIN(",",TRUE,IF(MID(A2,SEQUENCE(20,1,1,1),1)="a", SEQUENCE(20,1,1,1),""))

      This works in Office 365.
      In Excel 2019 try formula:

      =SUBSTITUTE(TRIM(CONCAT(IF(MID(A2,ROW(B1:B20),1)="a",ROW(B1:B20)," ")))," ",",")

      In earlier versions, it couldn't be done with a single formula.

      1. Hello! I need support to figure out solution for following equation;

        How to use IF, ISNUMBER and SEARCH functions to return exact match form number string like (1,2,3,4,5,10,11,12...). I use formula =IF(ISNUMBER(SEARCH("*1*",A1)),1,""), but it gives return value 1 to also to cell values like 7,8,10,11,12 as well when they are different and 1 is not mentioned. This is a for multiple choice questions, when respondents can choose morethan one option.

        I have been trying a lot, watched many videos, didn't get an answer. Please help!

        1. Hi!
          What result do you want to get exactly?
          To return the position number of a number in the text, you can use the formula

          =IFERROR(SEARCH("1",A1),"")

          1. Thanks for the reply.

            I want to search for 1 if number string like 1,11,12,13,14,15...and if it's 11,12,13,14,15...as blank, currently it returns one for both.

              1. Hello,

                I tried a different formula combination, First is =LEFT(A2,FIND("#",SUBSTITUTE(A2,",","#",1))-1), it gives me first option from LEFT. Now from a number string like 2,3,4 OR 3,4,5,6,7 I will get 2 from first and 3 from second.

                Using the same formula from RIGHT =RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,",","#",1))) gives me first number from right without delimits. Like from a number string 2,3 OR 4,5 it will give me 3 & 5. However, it will be helpful only if the number string is having two numbers like 3,4 OR 5,6 OR 7,8.

                My problem is I have more than 2 numbers and number string usually looks three, four OR five numbers (2,3,4.....5,6,7,8....5,6,7,6,7). In this case, the above formula will work only for LEFT side, right RIGHT it will not.

                Using the above formulas, could you please suggest how to figure it out (may be use of MID).

              2. Hello!
                I have no other option other than the one I have already suggested to you. Split the numbers by cell.

      2. Hi Alexander,

        This is brilliant. Thank you so much, it worked like a charm

        Pete

  13. Hello

    I have the text below.

    text text text 22-SU-22 text text text
    text text 22-SU-22-22Y text text text

    And I need to extract the 22-SU-22 and the 22-SU-22-22Y, the length of the extract text is not the same.

    Regards,

    1. Hello!
      Is there a general pattern in your text? Without this, it is impossible to write a formula. I don't see such a pattern yet.

  14. Hi,

    I could use your help with the following ask:
    I have a string of text in Cell A1: 51000 Amber TX AC. I separated in cell A2 to be 51000 and Cell A3 is TX AC.

    How can I use a formula to extract "Amber" from Cell A1 by using A2 and A3? I tried a Mid function: =mid(a1, len(a2)+1,find(A3,A2,1)-1), but this results in "Amber TX AC" instead of just "Amber"

    Any help is much appreciated!

    1. Hello!
      To find the position of the Nth occurrence of space in cell A1, use the formula

      =FIND("#",SUBSTITUTE(A1," ","#",1))

      Read more about this in this guide.
      Replace 1 with the desired number.
      To extract the 2nd word, you can use the formula

      =MID(A1,FIND("#",SUBSTITUTE(A1," ","#",1))+1, FIND("#",SUBSTITUTE(A1," ","#",2))- FIND("#",SUBSTITUTE(A1," ","#",1)))

      This should solve your task.

  15. Hey there, I'm trying to pull the data from right of the last " - " of these cell (my product description separating with spaces " - ") but can't seem to get it to work with all the values since some have a "-" in the name and some have 3 " - " and others have 4 separating different parts of the name.

    Bob J - 727 - 18-10102 - GPA Services - GPA Hourly Session (2-4 people)
    Spencer M - 717 - 10301 - GPA Transportation fee
    Spencer M - 717 - 10202 - GPA Materials- GPA Printed Materials Fee (Phase 2)
    Spencer M - 717 - 18-10102 - GPA Services - GPA Hourly Session (2-4 people)
    Soumya S - 718 - 10301 - GPA Transportation fee
    Soumya S - 718 - 10202 - GPA Materials - GPA Printed Materials Fee (Phase 2)
    Soumya S - 718 - 18-10102 - GPA Services - GPA Hourly Session (2-4 people)
    Emily S - 719 - 10301 - GPA Transportation fee
    Emily S - 719 - 10202 - GPA Materials- GPA Printed Materials Fee (Phase 2)
    Emily S - 719 - 18-10102 - GPA Services - GPA Hourly Session (2-4 people)
    Tom H - 730 - 10301 - GPA Transportation fee

    Thank You!!

    1. Hello!
      To return text after the last "–" character, use the formula:

      =MID(A1,SEARCH("#",SUBSTITUTE(A1,"–","#", LEN(A1)-LEN(SUBSTITUTE(A1,"–",""))))+1,100)

      Hope this is what you need.

      1. When I paste it and substitute my cell I get a #VALUE!

        1. Hello!
          Replace the em dash with a short dash in the formula. You will get the text after the last dash.

          1. we have below text lines

            TX01 INR 413IN TX02 INR 186K3 TX03 INR 236P2
            TX04 INR 130YR

            &
            we have below column

            YR
            YR
            IN
            K3
            P2
            WO
            YM

            how we find the above mentioned components and get the value before that in as per component wise

            1. as per earlier we need data should be come like below.
              manually illustrated.

              component image 1 image 2 image 3
              YR 130 80 130
              YR 50
              IN 413 413
              K3 186 724 372
              P2 236 236 236
              WO
              YM 142

              test image 1
              TX01 INR 413IN TX02 INR 186K3 TX03 INR 236P2
              TX04 INR 130YR
              test image 2
              TX01 INR 80YR TX02 INR 50YR TX03 INR 413IN
              TX04 INR 724K3 TX05 INR 236P2
              test image 3
              TX01 INR 372K3 TX02 INR 236P2 TX03 INR 142YM
              TX04 INR 130YR

              1. Dear Sir,

                below is my text in a1 & a2 cell
                we need to find the "yr" text in cell a1 as well as cell a2.
                after find "yr" text in cell a1 or a2 copy the value before that text showing (left side and it will 3 to 4 digit ) i.g. 130. to c1 cell
                so how we use excel formula for that

                TX01 INR 413IN TX02 INR 186K3 TX03 INR 236P2
                TX04 INR 130YR

  16. Please can you help!

    I am trying to extract text from cells e.g PID1234, PID99, PID 67814 (see examples of data below)

    But these strings could be anywhere in a longer string of text and the length of the number part after PID could vary. There should always be a space before the text PID and a space after the last number.

    For example,

    column A data could be:

    Row 1 - Ann Smith PID678 Mayo Nikki
    Row 2 - creary m c PID95678
    Row 3 - John smith PID54 000345 John smith PID54

    I need a formula that will only return the number part after PID until the space after the number part. I am looking for a formula that would return the results from the data in Rows 1 - Row 3

    Note: Row 3 is an example where the PID is populated twice, but would always be the same value.

    Results required:
    Row 1 - 678
    Row 2 - 95678
    Row 3 - 54

    I can’t use Left, Right or Mid to get my results and am now at a loss! Be so grateful if you can help me.

    Thanks

    1. Dear Sir,

      Thank you so much for your response and it is very help full to us.

      below is my text in a1 & a2 cell
      we need to find the "yr" text in cell a1 as well as cell a2.
      after find "yr" text in cell a1 or a2 copy the value before that text showing (left side and it will 3 to 4 digit ) i.g. 130. to c1 cell
      so how we use excel formula for that

      TX01 INR 413IN TX02 INR 186K3 TX03 INR 236P2
      TX04 INR 130YR

      Your response is : =MID(A2,SEARCH("YR",A2)-4,4)

      Reference to above my earlier query :

      Now my "yr" text and their value place is not fix some time in a1,a2,a3 cell near "IN" , "K3" , "P2".
      now how will find that text at a time in a1,a2,a3 cell and get that value in b1 cell using excel formula.
      example is below:
      1.
      TX04 INR 978K3 TX05 INR 944P2 TX06 INR 91WO
      TX01 INR 320YM TX02 INR 200YR TX03 INR 476IN
      2.
      TX04 INR 978K3 TX05 INR 944P2 TX06 INR 91WO
      TX01 INR 320YR TX02 INR 200YM TX03 INR 476IN
      3.
      TX01 INR 320YR TX02 INR 200YR TX03 INR 944P2
      TX04 INR 978K3 TX05 INR 944IN TX06 INR 91WO
      TX07 INR 284YM

      We looking forward on this quay.

      Thanks & regards

    2. Hi Sue,

      Please try this :

      =MID(E93,FIND("PID",E93)+3,(IF(IFERROR(FIND(" ",E93,FIND("PID",E93)),0)=0,LEN(E93)-(FIND("PID",E93)+2),FIND(" ",E93,FIND("PID",E93))-(FIND("PID",E93)+3))))

      I am using the MID formula here to extract the numbers after PID assuming the data is present E93 cell

      First Parameter is the Cell E93

      Second Paramter is Start_Num that is basically from which position to begin the extraction which is achieved by the FIND function (FIND("PID",E93)+3)

      Third Parameter is the num_characters to extact which tries to find the SPACE after the PID number and subtract the position of the number starting after PID, incase there is no SPACE after PID number as in your Row 2 example, it subtracts the position of the number after the PID from the length of the string

      Hope this helps !!

      Thanks

      1. Hello,

        How might I go about using this formula but for multiple instances? I have a long string with multiple instances of "HP" followed by a 3 or 4 digit number followed by a space.

        E.g "Template=J3 Primer=HP3271 Well=1 Notes=Low concentration of sample!!! Primers in stock Template=J3 Primer=HP2500 Well=2 Notes=Low concentration of sample!!! Primers in stock Template=J3 Primer=HP010 Well=3 Notes=Low concentration of sample!!! Primers in stock Template=J3 Primer=HP3274 "

        And I would like it to return "HP3271, HP2500, HP010, HP3274" to a single cell.

        Any help will be much appreciated.

  17. I have a field that I want to find a specific text (word) in a column. the values in this column are a reference to a location in another tab of the workbook (more specifically, the column is result of an Index/Match formula). I see the actual text in the field column but when I search on a specific word it doesn't find it. How do I get around this?

    1. Cell formula (in worksheet "B":
      =IFERROR(INDEX('FARA CAS HWT'!N$4:N$288,MATCH($B33,'FARA CAS HWT'!$G$4:$G$288,),0),"")

      Cell formula result display (in worksheet "B"): "Pin" Formula is retrieving this result from another tab/worksheet ("A") where "Pin" is listed in a table.

      When I do a "find "Pin"" (ctl F) it can't find it in worksheet ("B") even thought it displays "Pin" in the cell.

      thanks.

  18. Sheet1 - Col A contains Address of customer ( Cell contains more than 100 characters) - 10000 rows
    Sheet 2- Col A contains Names of places - 29000 rows
    I Require to find out whether the place is available in Sheet1- Col A. If available to display name of the place in Col B of Sheet1

    Any solution?

      1. Straight VLOOKUP formula is not working as the name of place in Sheet2 is a single word which should match with a part of the address .
        Suppose I have
        '23,Safdarjanng Enclave, New Delhi in Col A Row 2 of Sheet1'
        '56, CCCC Street, Hyderabad' in Col A Row 3 of Sheet 1

        'Mumbai' in Row 2 of Col A of Sheet2
        'Vizag' in Row 3 of Col A of sheet2
        'Chennai' in Row 4 of Col A of sheet2
        'Hyderabad' in Row 5 of Col A of sheet2
        'New Delhi' in Row 6 of Col A of sheet2

        search for places as per Col A of Sheet 2 in Col A of sheet1 and 'New Delhi' should be placed in Row 2, Col B.

        Thanks for sharing the link. Will go through the same. Whether any solution for the example given?

          1. Thanks for the help. Will try and give feedback

  19. How can I find a character and count the total of them in a whole spreadsheet for example I want to find how many "x" in the whole spreadsheet.

  20. Hello

    I have read through your comments but could not find what I am looking for.

    What I am trying to do is extract the following text "Technical" from a subject heading such as:

    Non-Confidential - Technical : Investigations
    Confidential - Technical : Meeting

    Is there a formula that picks up the text between "-" and ":" that will extract the text "Technical"?

    Look forward to your response.

    Thanks,
    Marty

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =MID(A2,FIND("–",A2)+1,FIND(":",A2)-FIND("–",A2)-1)

      We have a tool that can solve your task in a couple of clicks. This is the Extract text tool. It can extract text from a cell by pattern and in many other ways. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  21. Hi,
    I have had a look through all the great questions / answers but can't find one that fits my question.
    I have a number of server names which i want to remove extra characters such as:-

    LWWITE13(VG) - Remove (VG)
    HLHDDI02_DR - Remove _DR
    PORTING-VIOS - removed _VOS
    DC3_ESX_LWUKHYPTC02 - Removed DC3_ESX

    At the moment i use this formula and then amending ,FIND("(", for each line i find.

    =IFERROR(LEFT(A13,FIND("_",A13)-1),A13)

    But wanted to understand if there was a way of creating a formula's that covers them all?

    Appreciate your help

    Paula

    1. Hello!
      For the first three values, you can use the formula

      =LEFT(A1,MAX(IFERROR(SEARCH({"(";"-";"_"},A1,1),0))-1)

      For the fourth value, try the formula

      =MID(MID(A1,MAX(IFERROR(SEARCH({"(";"-";"_"},A1),0))+1,100), MAX(IFERROR(SEARCH({"(";"-";"_"}, MID(A1, MAX(IFERROR(SEARCH({"(";"-";"_"},A1),0))+1,100)),0))+1,100)

      I hope it’ll be helpful.

  22. Dear Sir,
    Please help me with a formula which returns complete value with specific Text like in following table data contain 7 entries with different months, any formula here I can use to search all entries and return complete value which contain text "MAY".
    Data
    A1 A-MAY212
    A2 B-MARCH212
    A3 C-APRIL213
    A4 D-APRIL214
    A5 E-JUNE215
    A6 F-JULY216
    A7 A-MAY217

    Result
    A-MAY212
    A-MAY217

    Thanks

    1. Hi!
      I believe the following formula will help you solve your task:

      =FILTER(A1:A9, ISNUMBER(SEARCH("MAY",A1:A9)), "")

      You can learn more about FILTER function in Excel in this article on our blog.

  23. Respected Sir,

    I have the following text in A1 Cell

    1 «kht¼{kt Eïhu ykfkþ yLku Ãk]Úðe MkßÞkO. 2 Ãk]Úðe yMíkÔÞMík yLku þqLÞ níke, yLku yøkkÄ s÷hkrþ WÃkh ytÄfkh AðkÞu÷ku níkku, yLku Ãkkýe Ãkh EïhLke þÂõík ½q{íke níke. 3 Eïhu fÌkwt, “«fkþ «økxku,” yLku «fkþ «økxâku.

    I want to extract the text written after each , in the new rows i.e. a3,a4,a5,a6 and so on.

    Please, help me I have around 200 such files containing similar data. Your guidance will help me a lot.

    Thank you Sir.

    -NelsonCM

    1. Hello!
      We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Data Tool.
      You can use the Convert Text function to replace the numbers with the # symbol. Then using the Split text - By Characters function, you can split the text into cells using the # separator.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      If you have any other questions, please don’t hesitate to ask.

  24. A1: FirstWord, B1: CharLen
    A2: January, B2: 4

    A5: SecondWord
    A6: nuraminis
    A7: literary
    A8: arbitrary
    A9: calendar
    A10: hopeful
    A11: sejanus

    Looking for a formula in the B column that would return TRUE for any instance of the CharLen matched in the second word... So B6-B8,B11 = TRUE the the others would return FALSE.

  25. Hello, i need help i have a phonetic checker file that will find the word in a cell that is inside this symbol (`), but unfortunately the formula that i created just can detect a one word, im planning to detect all the words inside the symbol in a cell

    like for example `James`, had a little lamb. The lamb is `white` and `curly`.
    My formula just detects the first word with (`) this symbol. I want it to be all of the words in a cell.

    =IFERROR(MID(A1,FIND("‘",A1)+0,FIND("’",A1,FIND("‘",A1)+1)-FIND("‘",A1)+1),"") here is my formula.

    1. Hello!
      It is impossible to solve your problem with one formula. I recommend using the "`"separator and splitting your phrase into columns using the Split Text tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      After applying Split Text, you remove all even columns from the result and get the words you want.

  26. Hello Alexander,

    Looks like you have your work cut out for you.

    I am sure you will be able to tell me the answer very quickly based on your experience.

    I have a list of part numbers in Column A :

    Column A

    11234
    11246
    15363
    12421
    14642

    I have a list of descriptions that may or may not contain the part numbers.
    I want to verify if that part number exists in a non-organised list (meaning they are not in the corresponding cell they could be anywhere in the column.

    If the part number exists within the description I want it to share it's adjacent cell which has a unique barcode.

    Hopefully that helps if you need further information let me know.

    Thanks,
    Adam

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(INDEX($E$1:$E$10,MATCH(TRUE, ISNUMBER(SEARCH(A1,$D$1:$D$10,1)),0)),"")

      $E$1:$E$10 - list of barcodes
      $D$1:$D$10 - list of descriptions
      I hope my advice will help you solve your task.

  27. "Description: Document Source Type: Email
    Record Locator: mary sherlene
    name: xxxxxxxxxxxxx
    Staging ID: 1111
    Fax Number Requested: No"

    Above data would be available on one cell, I want a formula to search for words " Record Locator: " and return name Mary sherlene
    similarly, I want to do search or other information like apply formula to search for words "name:" and return me xxxxxxxxxxxxx as result. note. values are not constant like name will not be always xxxxx it can be yyyyy.

    1. Hello!
      Please try the following formula:

      =MID(A1,SEARCH("Record Locator:",A1,1)+16, (SEARCH("name:",A1,1)) - (SEARCH("Record Locator",A1,1)+16)-1)

      I hope my advice will help you solve your task.

  28. Hi,

    I would really appreciate if you can help me. I have a list of 150 popular words (A1: A150). I have a set of 500 customers' comments (B1: B500). I want to write a function that for each customer comment, excel searches for the occurrence of any of the 150 popular words (A1: A150) and returns the frequency of their occurrence in a cell in front of each customer.

    For example, if I have 4 words (good, tasty, quite, fresh, like), and customer A said: "the meal was very tasty and fresh. I like the way food is presented. I also like the colours used in the place".
    In this comment, tasty used 1 time; fresh used 1 time; like used twice.

    I want the function to calculate the occurrence of these words as 4 times. I don't care about how many each word is repeated. I just need the total. I also want the function to be case insensitive since some customer might use capital case letters and other use lower case letters.

    Many thanks

    1. Hello!
      Please try the following formula:

      =SUM((LEN(A1)-LEN(SUBSTITUTE(A1,$D$1:$D$150,""))) / LEN($D$1:$D$150))

      where
      $D$1:$D$150 - list of 150 popular words

      I hope it’ll be helpful.

      1. Many thanks. It worked very well.

        I also updated it to be case insensitive by
        =SUM((LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER($D$1:$D$150),""))) / LEN($D$1:$D$150))

        Thanks again. Much appreciated

  29. Currently looking to extract text within an individual cell, using a list that contains characters in a list I created. The text in the list ranges from A0-Z10 as this is what is within all those cells that I am trying to extract. So for example Cell B2 reads "John Anderson H0468582 Dr. James Dean" This is just an example as the placing of the middle set of characters isn't always consistent. So i am trying to extract the H0468582 based off there being a "H0" in the list I created (which sits within a different tab of the excel sheet) in to cell H2 for example.

    I'm not sure if I explained it very well but if you can help then that would be great

    1. Hello!
      Please try the following formula:

      =LEFT(MID(A1,IF(SUM(IFERROR(SEARCH(G1:G10,A1,1),"")) > 0,SUM(IFERROR(SEARCH(G1:G10,A1,1),"")),""),100), SEARCH(" ",MID(A1,IF(SUM(IFERROR(SEARCH(G1:G10,A1,1),"")) > 0,SUM(IFERROR(SEARCH(G1:G10,A1,1),"")),""),100),1))

      G1:G10 - list ranges, no blanks.

      I hope it’ll be helpful.

  30. Dear Madam, I am searching a words content , an example

    Searching "100A TP 50KA EU" from sheet-1 to sheet-2 "100A TP ACB 50KA FIXED LSI FRANCE EU"

    Please help me to get suitable formula.

    1. Hi,
      You haven't written what result you want to get. To determine that the text was found, you can use the following formula

      =IF(IFERROR(SEARCH("*"&SUBSTITUTE(A1," ","*")&"*",B1,1),"")>0,"Yes","No")

      A1 is “100A TP 50KA EU”.
      I hope it’ll be helpful.

  31. =LEFT(C3;SEARCH("/3/";C3)-1)
    =LEFT(C3;SEARCH("/2/";C3)-1)
    =LEFT(C3;SEARCH("/1/";C3)-1)

    how to sum up these three search criteria in one formula and when this value is missing to return the text to the cell

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =CONCAT(IFERROR(LEFT(C3,SEARCH({"/1/","/2/","/3/"},C3)-1),""))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  32. My post may be neither necessary nor useful, just my subjective feedback..
    Nevertheless, I need to thank Svetlana very much for the well-arranged, almost analytical tutorials, which have helped me a lot in my work, as well as this one right now.

    Thanks a lot, Kind Regards.

    1. Hi Jaroslav,

      Thank you so much about your kind words about my work. I'm delighted to hear that our tutorials are useful to you!

  33. Hi,

    I would really appreciate if you can help me. I have a list of 150 popular words (A1: A150). I have a set of 500 customers' comments (B1: B500). I want to write a function that for each customer comment, excel searches for the occurrence of any of the 150 popular words (A1: A150) and returns the frequency of their occurrence in a cell in front of each customer.

    For example, if I have 4 words (good, tasty, quite, fresh, like), and customer A said: "the meal was very tasty and fresh. I like the way food is presented. I also like the colours used in the place".
    In this comment, tasty used 1 time; fresh used 1 time; like used twice.

    I want the function to calculate the occurrence of these words as 4 times. I don't care about how many each word is repeated. I just need the total. I also want the function to be case insensitive since some customer might use capital case letters and other use lower case letters.

    I used the following formula to count the frequency of one word, but can't adopt it to search for the 150 words in the word list I have in A1:A150

    C1=ISNUMBER(SEARCH("like",B1))

    I really appreciate your help.

    1. I would really appreciate if you can help me with this request. I am struggling to work it out.

  34. hi, i would like to extract data from 1 sheet to other sheet .Like example , status have 3 input there, that is completed, cancel, shipping , i wan extract all the data regarding shipping. but i just can get 1 data of shipping. how can i do with extract all data regarding it

      1. I had tried the method but it direct show with wrong
        this is what i type :
        =IFERROR(INDEX(orders!$A$2:A,SMALL(IF($A$16=orders!$B$2:B,ROW(orders!$A$2:A)-MIN(ROW())+1,""),ROW()-1)),"wrong")

  35. The column AG contains the combination of below, need to separate seconds, minute, hour,day,week, months in different column.

    0s
    32m 14s
    3h 21m 18s
    1w 1d 12h 0m 28s
    6d 18h 36m 50s
    1mos 2w 2d 9h 6m 56s

    please help me with formula

      1. Please refer my expected result in Excel, kindly assist me, alignment is not correct.

        Resolution Time Months Weeks Days Hours Minutes Seconds
        0s 0 0 0 0 0 0
        13m 17s 0 0 0 0 13 17
        1h 48m 15s 0 0 0 1 48 15
        4d 9h 27m 40s 0 0 4 9 27 40
        1w 6d 7h 24m 12s 0 1 6 7 24 12
        6d 16h 17m 38s 0 0 6 16 17 38
        6h 13m 34s 0 0 0 6 13 34
        1w 6d 7h 24m 12s 0 1 6 7 24 12
        9h 0m 0s 0 0 0 9 0 0
        1mos 2w 2d 9h 6m 56s 1 2 2 9 6 56

        1. Hello!
          The formula below will do the trick for you:

          =REPT("0 ",5-(LEN(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$93),1)), MID(H1,ROW($1:$93),1)," ")))," "," ")) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$93),1)), MID(H1,ROW($1:$93),1)," ")))," "," ")," ","")))) & SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$93),1)), MID(H1,ROW($1:$93),1)," ")))," "," ")

          Divide the resulting text into columns, as I advised you earlier.
          I'd recommend you to have a look at our Ablebits Tools - Split text tool that can help you split the text to columns. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

          1. tried the free tool, but not getting the expected result. Formula applied in one column, need to be in separate column.

  36. Hi, Would like to ask you help for this scenario below:
    For example I have this data below in a column, and I want to search for which rows have "John" only in their name.
    1.Johnson New
    2.Jane John
    3.Earl John Watson
    4.Amy Anne John

    With the data in Row 1, it returns a value for it because it contains "John" in "Johnson" but I won't be needing that because I need only the "John" name.
    How can I do this in a formula? Thanks!

    1. Hello!
      To find an exact match of a word in the text, you can use this formula:

      =IF((ISNUMBER(FIND($A$1&" ",A2))+ISNUMBER(FIND(" "&$A$1,A2))+($A$1=A2))>0,TRUE,"")

      where cell A1 contains the word that we are looking for in the text.
      I hope my advice will help you solve your task.

    2. adding more data:

      5.John Watsons
      6.John Johnsons

  37. I read through your article and most of the questions but haven't found a way of removing multiple instances that occur in a single cell. When I import a data table of projects with employee names and occupations from our system the information comes into an Excel table with the project number as the row and the various occupations as the columns. The names are displayed with system generated characters and id numbers.

    For example for project manager the name of Sue Smith shows in the table as Smith,Sue;#56789. For occupations with a single entry I can use left and search functions to remove the";#56789" using =LEFT(A1,SEARCH(";",A1)-1). The problem comes in when I get multiple people for a occupation. For example for engineer I may get Jones, John;#1234;#Trifuntov,Alexander;#789;#Sharashova,Natalia;#90. If I use the above formula all I get is Jones, John and the other names are truncated. I am looking to have it come up as Jones, John;Trifuntov, Alexander;Sharashova,Natalia I have tries using REPLACE and SUBSTITUTE but due to the varying number of people, length of names and id numbers I keep getting issues. Is there any way to use a find/search formulas?

    1. Hello!
      For your example text, you can use the formula

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER(--MID(A1,ROW($1:$91),1))),MID(A1,ROW($1:$91),1),"")))," ",""),"#;#",""),";#","")

      It removes from the text all numbers and extra characters "#" and ";"
      Hope this is what you need.

      1. I really appreciate your fast response. When I use the above formula I get an error. Maybe we can start simple and once I get that correct I can expand. Let's say

        Jones, John;#1234;#Trifuntov,Alexander;#789;#Sharashova,Natalia;#90 is in cell A1

        and I want to look like

        Jones, John; Trifuntov,Alexander; Sharashova,Natalia in cell B1.

        What would the formula be in cell B1?

        Again thanks and I appreciate your patience!

  38. Hello,
    I would very much appreciate any suggestions/guidance to find a specific text, "Apple," in the middle and at the end where there is no space in a cell. (Yes.. I am sorry and am feeling bad to have to ask this ignorant question as I am a new user.)

    Here's my formula to find the text "Apple" in the middle.
    =MID(A1,FIND("#Apple",A1,1)+1,(FIND(";",A1,FIND("#Apple",A1)))-(FIND("#Apple",A1,1)+1))

    Column A
    A1 - 15;#Apple;#121;#Pears - Organic;#18;#Banana;#87;#Strawberry;#38;Cherry;#149;#Orange
    A2 - 18;#Banana;#87;#Strawberry;#38;#Cherry;#15;#Apple;#121;#Pears - Organic
    A3 - 16;#DragonFruit;#121;#Pears - Organic;#18;#Banana;#87;#Strawberry;#38;Cherry;#15;#Apple

    Column B with the MID Formula
    B1 - Apple
    B2 - Apple
    B3 - #VALUE <-- because Apple is at the end

    I need a formula that would get both middle and end....

    Appreciate any suggestions/guidance.

    Kind regards,
    Miko

    1. Hello Miko!
      If you just need to extract "Apple" from the text, then you can just take 5 characters:

      =MID(A1,FIND("#Apple",A1,1)+1,5)

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

      1. I cannot thank you enough. You saved hours of my time to prepare a weekly report out of crude SharePoint datadump.
        Gratefully,
        Miko

  39. Hi,
    I would like to search for a specifik text within a column. Example: If a cell cointains: "The upper side. System:20 Tag:20A-VA004" I would like to copy the tag number (Tag:20A-VA004) to a new cell, both tag written TAG, tag: and Tag. I have tried the formula =TRIM(LEFT(SUBSTITUTE(MID(C12;FIND("Tag:";C12);LEN(C12));" ";REPT(" ";100));100)), but this does not include space and different writings. Can you please help?
    Regards, Kristin

    1. Hello!
      If I understand your task correctly, to extract text starting at some position, use the formula

      =MID(D1,SEARCH("tag",D1,1),LEN(D1)-SEARCH("tag",D1,1)+1)

      I hope it’ll be helpful.

  40. Good day! I am struggling to work on this one. I need to mark the 50th word in a paragraph with * (still with the whole paragraph, im not just gonna extract the 50th word out) but if it is equal or less then 50 words, then I can just copy the text as it is. How can I do this? Thank you for your help!

    1. Hello!
      If I understand your task correctly, to put * before the 50th word, use the formula

      =IF(LEN(C1)-LEN(SUBSTITUTE(C1," ",""))<51,LEN(C1)-LEN(SUBSTITUTE(C1," ","")),SUBSTITUTE(C1," ","*",50))

      I hope this will help

  41. Respected Sirs,

    Here I have a problem to solve
    in a coloumn of name there are three or two words name
    Ram Bahadur Gautam
    sanjay Limbu

    I need to extract only last sir name to another column and copy the formula
    Thank your for your kindly assistance
    Sanay limbu

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20))

      This will help to extract the last word from the text.

  42. Cell A1 contains the googlefinanceticker NASDAQ:TQQQ
    Cell B1 should contain a Formula wich verifies if A1 contains a colon. If YES than got the name of the ticker.
    The idea should be like this, but I have problems with combination of funktion and syntaxis.
    =IF(ISNUMBER(FIND(":";P2));AA2=GOOGLEFINANCE(AP2;"PRICE");"ist kein googlefinanceID")
    Any idea !!! Thanks in advance for your help,
    Valentin

    1. Hello Valentin,

      If I've got your task correctly and you work in Google Sheets, here's how your formula should look like:
      =IF(ISNUMBER(FIND(":",P2)),GOOGLEFINANCE(P2,"PRICE"),"ist kein googlefinanceID")

      Please refer to the following article about the IF function in Google Sheets to learn the correct syntax: https://www.ablebits.com/office-addins-blog/if-function-google-sheets/
      There's even a tool that can figure out the syntax for you.

  43. Hi,
    I want your help in finding a formula where i can pull of data from multiple row - having multiple data in each cell - and the cell has some key identifier at there first to look out the data.
    For e.g. In row Cell 1- 32:hshsjsjsbsvshh
    Cell2: 32:shsjsjsjsjsjsjjs
    Cell3: 67:hwhsshshshshsh
    Cell4: 69:hshsshsusushsh

    So in above example i want the data in next sheet from the cell which is started from '32:'

    1. Hello!
      You did not say exactly how you want to extract the data. Suppose the data needs to be combined. If I understand your task correctly, Merge cells by condition using the array formula

      =CONCAT(IF(LEFT(E2:E10,3)="32:",E2:E10,""))

      Press Ctrl + Shift + Enter so that array function to work.
      We merged all the cells that start with "32:"

      1. Hi Alex,

        Thanks for your reply but here i don't want to merged the cell, I want to pull out the data start with 32: as it is in the other excel sheet.
        Hope you understand my query.

  44. Hi,
    I'm trying to write an equation in Excel that rounds using the following rules:
    1. If the extra digit is less than 5, drop the digit.
    2. If the extra digit is greater than 5, drop it and increase the previous digit by one.
    3. If the extra digit is five, then increase the previous digit by one if it is odd; otherwise do not change the previous digit.

    I feel like I'm close to a solution by using the IF(AND(Find line of commands but can't get it to come together properly. Could you please help?
    Thanks so much,
    John

    1. Hello John!
      The Find function is applied to the text. In this case, I assume that we have a number. Apply the IF function and rounding function.

      =IF(MOD(INT(A30),2)=0,IF(A30-INT(A30)<=0.5,INT(A30),ROUND(A30,0)),ROUNDUP(A30,0))

      Hope this is what you need.

      1. Thank you so much for your response Alexander!
        I tried that function but didn't have much success. Maybe if I give you an example that would help.
        If I have a number like 0.20645 and I would like it rounded to four decimal places and be rounded down if the value in the fourth place is even and the fifth value is =5, in this case 0.20645 would be rounded to 0.2064. On the flip side of that I would still like a value like 0.20615 to be rounded to four places but rounded down if the fourth value is odd and =5. In other words 0.20615 would be rounded to 0.2062.

        This is the equation I've tried without success: =IF(AND(ISODD(MID(H12,FIND(".",H12)+4,4)),MID(H12,FIND(".",H12)+5,4)="5"),ROUNDUP(H12,4),IF(AND(ISEVEN(MID(H12,FIND(".",H12)+4,4)),MID(H12,FIND(".",H12)+5,4)="5"),ROUNDDOWN(H12,4),ROUNDDOWN(H12,4)))

  45. Hi Sir,
    In one column i have a string from which i have to extract the exact unique code which starts with "ABCD" and followed by 4 numeral characters (Example-ABCD2345)
    Example String-
    Information: ABCD: Update for Application 1 (ABCD2140) Rel. 1.01
    Expected output- ABCD2140
    Solution- =IFERROR(MID(C3,FIND("(ABCD",C3),FIND(")",C3)-FIND("(ABCD",C3)),"NA")
    The solution works for above String, but it fails in cases where any "(" comes before "(ABCD".
    Example-(XYZ) external – Application 2 (ABCD2003) Rel. 1.02
    Expected Output- ABCD2003
    Could you help me get a common solution which works for both the cases.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      ="ABCD"&MID(C3,FIND("(ABCD",C3,1)+5,4)

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

  46. I have a string of text, similar to the following: This well requires cleaning; Y; $2,000; WB Abandonment; Y; $12,000; Other; N; $5,000.
    I'd like to use a formula to search the string and if "WB Abandonment; Y;" is in the string it would return "$12,000". If "WB Abandonment; Y;" is not in the string, it returns blank. I'm struggling with the mid search formula since the various lines of text have a different number of components the well requires. Any help you can provide would be greatly appreciated.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(IFERROR(SEARCH("WB Abandonment; Y;",A1,1),0) > 0,"$ 12,000","")

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

      1. Sorry, I wasn't clear. The value isn't always $ 12,000. I'm looking to return the value that follows "WB Abandonment; Y;".

        1. Hello Sue!
          To get all the text after phrase “WB Abandonment; Y;”, use the formula

          =IF(IFERROR(SEARCH("WB Abandonment; Y;",A1,1),0)>0, MID(A1,SEARCH("WB Abandonment; Y;",A1,1)+19,100),"")

          If you wrote exactly what you want to receive, we would solve your problem faster.

          1. Unfortunately this formula returns all the text after "WB Abandonment; Y;". I'm hoping to be able to only pull the $ amount after "WB Abandonment; Y;" and before the next ;.

  47. I have been trying to fix this for days and it has me baffled.

    The customer enters their postcode and I am trying to match it with a postcode area. Sometimes the customers put a space in their postcode and sometimes they don't, so I have a list of postcodes with a space and a list without a space and I need to search both lists but cannot get it to work when the postcode is entered without a space.

    I have created a postcode sheet with Column A being postcodes with a space and Column B without a space and column C is the area match

    For example, customer's postcode is "DL1 2NE" in cell Q2 on "Enquiries" sheet and I want to look to see if there is a match in the "Postcodes" sheet and if not put a value of "No"

    Postcodes

    Column A Column B Column C
    TS1 2TR TS12TR Tees
    DL1 2NE DL12NE Tees

    =IF(ISNA(VLOOKUP(Q2,'postcodes'!A:C,3,FALSE)),"No",VLOOKUP(Enquiries!Q2,'postcodes'!A:C,3,FALSE))

    This works when there is a space but returns the wrong value when there is no space (i.e. it should still find a match and show "Tees" but instead it results in "No"

    Thank you for your consideration.

    Nick

    1. Hello Nick!
      Remove spaces from the index using the SUBSTITUTE function. Instead of Q2, use SUBSTITUTE (Q2, " ", ""). Search for this value in column B of the “Postcodes” sheet, where there are also no spaces.
      I hope it’ll be helpful.

  48. Good morning,
    I have an a column that I put monitored voltages in and these values will change each time I take the measurements. At the bottom of the column I have the =min and =max formula so I know those values. My question:
    is it possible to use the Find or Search formula to locate the =max in the column?

    cell #
    30 3.9000
    29 3.9000
    28 3.9002
    27 3.9008
    26 3.8841
    25 3.9000
    24 3.9000
    23 3.9002
    22 3.9009
    21 3.9754

    67 3.9010
    68 3.9000
    69 3.9002
    70 3.9008
    71 3.8847
    72 3.9000
    73 3.9000
    74 3.9002
    75 3.9009
    76 3.9748

    =max 3.9754
    =min 3.8841
    I would like to find the cell# that is =max and have it automatically highlighted.

    Thanks for any assistance that you can offer.
    Best Regards,
    Robert

    1. Hello Robert!
      To automatically highlight the maximum or minimum value with color, you do not need to use the formula. Use standard conditional formatting tools. Conditional Formatting - Top / Bottom Rules. Read more here.

  49. Hello Sir,
    I have read all of the comments but unfortunately could not find solution for my problem yet.
    I have an excel with many rows which containing a large text. (from 20 to 500 words)
    I need way to find multiple key words within that text and if it's possible to display or to direct me to that text. For example let's say I have a text " In order to purchase the item, you need more money" and i want to search "order" and "money".
    So basically I need a search function with more than one string.
    Thanks a lot.

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

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