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

  1. I have uneven text in multiple columns, i have used IF condition to get the text from all columns in one column. But i want the text with some exception. If the text is having either of word "-offline" or " - offline" , it should ignore those part from text. Hyphens are in many cells but I want to ignore this only wherever in the text it is with offline. I have used the mid and search function to resolve the issue, but ,wherever there is a text "-offline" it is not looking for that as i have embedded the text " - offline" in my search formula. What else i can use in my formula so that it can work for the text "-offline".

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      1. Hi Alex, thanks for your reply, I am sorry for not been clear here. I have below data for eg

        Column A Column B Consolidated
        Bajaj Insur - Offline Payment Bajaj Insur
        Jacksupplies - offline payment Jacksupplies
        Dyne Corporation - Offline Dyne Corporation
        clubhouse.com-Offline Payment clubhouse.com-Offline Payment

        I need to get both columns in One one column "Consolidated", but I want wherever there is a text with "offline", my formula should check for immediate space and "-" and immediate spaces before "-" if any and exclude those parts from the text like "-Offline" or " - offline". I have used the term "offline" , so that if in any cell offline is without the text "payment", that also can be looked and excluded. Eg Column A line 3 there is only "offline". For this I have used below formula.

        =IF(AND(A2="",B2=""),C2,IF(A2"",IFERROR(MID(A2,1,SEARCH(" - Offline",A2,1)-1),A2),IF(A2="",IFERROR(MID(B2,1,SEARCH(" - Offline",B2,1)-1),B2))))

        However you can see, in my last line I am getting the column value with the text "-Offline Payment". I want to what can be the additional function used here to avoid these kind of cases, using my formula.

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

          =IFERROR(LEFT(A2,SEARCH("##",SUBSTITUTE(A2,"-","##",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1),"")

          Hope this is what you need.

          1. I cannot use substitute here, as I have "-" in other cells as well without text "Offline". I want to exclude "-" only from the cell wherever it is with space and " offline".

            I have other text as

            Jyoti_ Jyoti (result should come as it is)

            Amr_ amar_ amit (result should come as it is)

            Summon (result should come as it is)

            Amr-Offline (result should exclude "-Offline. In this case I dont have space before and after hyphen, Result should exclude hyphen and text "offline")

            Jennifer - Offline Payment (result should exclude " - Offline", In this case I have space before and after hyphen, it should exclude both space with the text "offline")

            Let me know if I am clear with my question now.

            1. Hi!
              The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. If you could give a complete explanation, you would have saved a lot of your time and my time.
              Try the following formula:

              =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"- offline","",1),"- Offline","",1),"-offline","",1),"-Offline","",1))

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

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

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

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

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

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

  8. Super helpful! thank you!!!!

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

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

  11. "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.

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

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

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

  15. =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.

  16. Hi

    need help to match number from 2 different columns and the result is true if matching for example in one column i have 12345 and that is randomly placed in the other column, so if column A and Column B has 12345 the result should be true or else it should be false

  17. I am trying to make a department shift schedule where I list the start times and fill in names, then transfer the information to a standard schedule. For example, if Sue's name is in the first 3 cells, then she is scheduled for 6:30A, if in the next 3 cells, then 7:00A, etc. What function will give me a value based on which cell her name is in?

  18. Hello! I need help expanding on the concept of finding text between parenthesis. I have a cell with lots of lines of text between parenthesis and I want to find all the instances of words between parenthesis in this single cell, not just the first one. How do I get the formula =MID(P2,SEARCH("",P2)-SEARCH("<",P2)-1) to repeat for the second, third, fourth instances etc. of text between parenthesis?

  19. Hi,

    I want to extract the Invoice Number from the column of comments with different texts.
    So, the first check is whether the cell has an Invoice number, If yes, then I need to extract the 4 digit number from the text string.

    Please guide how I can do that.

    Reverse - Standby Invoice 7286 posted twice
    BNP Security - LND 11697 Receipt No 9613 - CHERRYBROOK for 13/9 - 31/10/19
    BNP Security - LND 11697 Receipt No 9616 - NORWEST for 13/9 - 31/10/19
    BNP Security - LND 11697 Receipt No 8252 - 129 Showground for Aug-19
    Standby Invoice 7432 for Epping for May-20

  20. Dear Sir ,
    I have a list I need to do sorting based on 4 character or 5 digit from behind below are example.
    appreciated if can help to sort or separate it will be easier rather than I do it manually
    CB80W90D208 2
    CB80W90P20 8
    CBDEP0P16 2
    CBDEP0P18 0
    CBDEP2P18 223
    CBDEP2P20 2
    CBDGEAR100D208 0
    CBDGEAR68D208 2
    CBDST100D205 -9
    CBDST100D208 8
    CCOM100D205 4

    Thanks

  21. MilanoZalgiris
    Real MadridFenerbahce
    BarcelonaC.Zvezda
    ValenciaMaccabi T.A.
    NewcastleLondon Lions
    Townsville (F)Bendigo (F)
    Southside F. (F)Perth (F)
    FlamengoCampo Mourao
    PrometheusPAOK
    Xarilaos T.AEK
    Zielona GoraOstrow
    Anwil W.A.Gdynia
    Townsville (F)Bendigo (F)
    Southside F. (F)Perth (F)
    Sydney (F)Canberra (F)
    WelsBC Vienna
    Vienna DCA.Traiskirchen
    OberwartKlosterneuburg
    FlamengoCampo Mourao
    Sao PauloFranca SP
    MinasUnifacisa
    BrasíliaPinheiros
    KolinOstrava
    ETHA E.Apop Paphos
    Apoel N.E.N.Paralimni
    OmoniaAEL Limassol
    HermesAlkar
    SkrljevoSibenik
    ZabokZadar
    HorsensHerlev W.
    AmagerSvendborg
    KobratKTP Basket
    Helsinki S.UU-Korihait
    Kaarinan U.B.Salon Vilpas
    Mac.RishonB.Herzelia
    L.RytasNeptunas
    PrienaiPieno
    FyllingenTromso
    C.T.OsloNidaros J.
    Asker AliensBaerums
    EstudiantesUnicaja
    Real BetisBilbao
    ValenciaTenerife
    ZaragozaBarcelona
    Real MadridManresa
    CantuVirtus Roma
    PesaroR.Emilia
    Fortitudo Bol.Virtus Bologna
    TrentoVarese
    BrindisiBrescia
    MilanoVenezia
    WurzburgHamburg
    BayreuthMitteldeutscher
    Alba BerlinBonn
    Bayern M.Ludwigsburg
    CrailsheimBamberg
    IraklisPeristeri
    LavriouIonikos N.
    PanathinaikosKolossos
    Dabrowa G.S.Wroclaw
    StargardStarogard G.
    BrasíliaPinheiros

    Hi. Can anyone help me with a formula or vba to split these strings by the continuous middle continuous substing it contains? eg. Dabrowa G.S.Wroclaw in Dabrowa G.S. - Wroclaw. THX

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

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

  24. I have a query where I need to find any punctuation characters in a list of names (where they have accidentally typed something wrong). It makes sense to repeat the formula for each character I am trying to find which is fine but all find and search formulas seem to only search one cell and I'd like to put a range in there. Is there something I can use to search the whole range? or even a way of making the LEN() formula search a range as that would do the job. thanks

  25. Sir/Mam,

    In excel, i have a bundle of statements(A2) and other cell i have specific values(B2) with pipelines as separators within the value. Now, what formula to be used to compare the specific values if present in the statement or not and the output to highlight the value in the specific cell - B2

    1. Hello!
      Your task is not completely clear to me. Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

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

        1. Hello!
          Unfortunately, without seeing your data it is impossible to give you advice.

          I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

          We'll look into your task and try to help.

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

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

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

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

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

  32. Hi,

    I need to extract number from strings like:
    #34224 some text
    #43543543: some text
    #34243-some text
    #3423145
    so number can be different in length and be followed by space OR semicolon OR dash OR do not have any character after.
    the formula that I have for the third case is =IFERROR(NUMBERVALUE(IF(SEARCH("#";[@Comment])=1;MID([@Comment];2;SEARCH("-";[@Comment])-2);FALSE));0)
    but how to modify it to include all 4 cases?

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

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

  35. Hi Alex! Great tutorials! I lead a group of sales coaches which write their feedback in an excel sheet. I am trying to build a formula which searches a keyword or phrase within a paragraph and the word or phrase after it ( Ex. keyword:OPPORTUNITY: FACT FINDING) Then, I would keep track how many times the phrase after the keyword was used. Any thoughts?

    1. Hello Juan!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail.Is the feedback written in several cells or in one? Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

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

  37. i want to use =RİGHT operation and every word contain ")" for ex asdasdasd),asdasdewqd),uymjutynm), but thera are a so many blanks and i want to delete blanks and i want to assigned them a number for exam. asdasdasd) = 123 asdasdewqd)=432 uymjutynm)=564 , there are a few exist them and i want to convert to number to text with use a only 1 letter that ")" how can i figure out
    i think if i use matlab it facilitate my works or complicate my works
    sorry for my bad english

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

        1. hello!
          I don't want to guess anymore. Explain what kind of data you wanted to get from Cell 1-32: hshsjsjsbsvshh ??
          Write the expected result for each cell.

  39. I think I've figured it out on my own. Thank you anyway for your help.

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

  41. I have a column A with multiple cells having a common value.
    I also have a column B with equal number of cells each having a unique value.
    I want to do a FInd and Replace such that it finds the common value in each cell of Column A and replace it with the unique value of adjoining cell of column B,
    See screenshot for more clarity - https://prnt.sc/t0ksyn

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.The Find function can search in only one cell. I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What does "common value in each cell of Column A" mean? In column A, all cells have the same values. If a value is written in a cell, then it can be changed either manually or using the VBA macro. Therefore, the values in column A, you cannot change the values using the formula. Explain in more detail what you want to do.Thank you!

  42. Hello Sir, I need a Formula/Function to show variance with multiple criteria…
    Function needs applied to E:E in examples below- if it is needed to add extra columns, we can. If it needs to be done in VBA form, we can do that as well.
    A - FIND B:B WITHIN F:F
    SEARCH ( B:B , F:F )
    B - TAKE THE MINIMUM OF D:D, WHERE “A” CRITERIA MATCHES (MULTIPLE SKU WITH DIFFERENT PRICING)
    MIN ( D3 : D11 )
    C - AND THEN MULTIPLY THAT MINIMUM BY 1.25
    MIN ( D3 : D11 ) * 1.25
    D – TAKE THAT NEW NUMBER AND APPLY IT TO ALL ROWS IN COLUMN D WHERE “A” & “B” CRITERIA ARE TRUE
    (not sure how to do this part)
    RAW DATA
    1 B C D E F
    2 SKU MKT MARKET PRICE 25% VARIANCE SKUMKT
    3 1234 CHINA 18.98 1234CHINA
    4 1234 AUSTRALIA 18.98 1234AUSTRALIA
    5 1234 USA 17.03 1234USA
    6 9876 CHINA 17.11 9876CHINA
    7 9876 AUSTRALIA 18.98 9876AUSTRALIA
    8 9876 USA 18.28 9876USA
    9 4567 CHINA 18.98 4567CHINA
    10 4567 AUSTRALIA 18.98 4567AUSTRALIA
    11 4567 USA 18.63 4567USA

    WHAT IT SHOULD LOOK LIKE WHEN COMPLETE
    1 B C D E F
    2 SKU MKT MARKET PRICE 25% VARIANCE SKUMKT
    3 1234 CHINA 18.98 21.29 1234CHINA
    4 1234 AUSTRALIA 18.98 21.29 1234AUSTRALIA
    5 1234 USA 17.03 21.29 1234USA
    6 9876 CHINA 17.11 21.38 9876CHINA
    7 9876 AUSTRALIA 18.98 21.38 9876AUSTRALIA
    8 9876 USA 18.28 21.38 9876USA
    9 4567 CHINA 18.98 23.29 4567CHINA
    10 4567 AUSTRALIA 18.98 23.29 4567AUSTRALIA
    11 4567 USA 18.63 23.29 4567USA

    1. Am I not able to do a formula like what it is I am hoping for?

      1. Hello!
        I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example the expected result.
        It’ll help me understand it better and find a solution for you. Thank you.

        1. We need a Formula/Function to show variance with multiple criteria…
          Function needs applied to E:E in examples below.
          We need to find the same SKU numbers (column B) that have multiple markets (column C), find the lowest price of that SKU between those markets(column D), and then multiply that lowest price by 25% and be entered in column E for all of the SKUs that match.
          A - FIND B:B WITHIN F:F
          SEARCH ( B:B , F:F )
          B - TAKE THE MINIMUM OF D:D, WHERE “A” CRITERIA MATCHES (MULTIPLE SKU WITH DIFFERENT PRICING)
          MIN ( D3 : D11 )
          C - AND THEN MULTIPLY THAT MINIMUM BY 1.25
          MIN ( D3 : D11 ) * 1.25
          D – TAKE THAT NEW NUMBER AND APPLY IT TO ALL ROWS IN COLUMN D WHERE “A” & “B” CRITERIA ARE TRUE
          (not sure how to do this part)
          RAW DATA
          1.B.....C..........D..............E.............F
          2 SKU .MARKET.....MARKET PRICE. 25% VARIANCE. SKUMKT
          3 1234 CHINA ......18.98 ........()...........1234CHINA
          4 1234 AUSTRALIA ..18.98 .........()...........1234AUSTRALIA
          5 1234 USA ........17.03 .......().............1234USA
          6 9876 CHINA ......17.11..........().......... 9876CHINA
          7 9876 AUSTRALIA ..18.98 .........()...........9876AUSTRALIA
          8 9876 USA ........18.28..........().......... 9876USA
          9 4567 CHINA ......18.98 .........()...........4567CHINA
          10 4567 AUSTRALIA ..18.98.........().......... 4567AUSTRALIA
          11 4567 USA ........18.63 ........()............4567USA

          WHAT IT SHOULD LOOK LIKE WHEN COMPLETE
          1.B.....C..........D..............E...............F
          2 SKU .MARKET..... MARKET PRICE.. 25% VARIANCE ..SKUMKT
          3 1234 CHINA ......18.98 .........21.29 .........1234CHINA
          4 1234 AUSTRALIA ..18.98 .........21.29 .........1234AUSTRALIA
          5 1234 USA ........17.03 .........21.29 .........1234USA
          6 9876 CHINA ......17.11 .........21.38 .........9876CHINA
          7 9876 AUSTRALIA ..18.98 .........21.38 .........9876AUSTRALIA
          8 9876 USA ........18.28 .........21.38 .........9876USA
          9 4567 CHINA ......18.98 .........23.29 .........4567CHINA
          10 4567 AUSTRALIA .18.98 .........23.29 .........4567AUSTRALIA
          11 4567 USA .......18.63 .........23.29 .........4567USA

          1. Hello!
            Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  43. HI FOrum,
    If i need to extract text from intial and last name with no character break, how do i do it?

    1. Hello!
      I recommend to study this article. Then describe the problem in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

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

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

  46. Hi sir,
    I have a question, my question is that the way we have used the left function with find ,same way we can't use the right function with find to get the last name.

  47. Hi Alexander,
    Thank you for your post. I have been unable to find any information to help me with my issue - perhaps you can help?
    I have a text string in column L. Within this text string I can find a series of alternatives (which are contained in the OR brackets below). I've tried to simplify a heavy, clunky formula (1) to a more elegant (2), but (2) isn't working - it's not returning any values even if I know the value is contained in column L.

    (1) Monster: =IFERROR(IFERROR(IFERROR(IFERROR((IFERROR(MID(L2,FIND("DPTO",L2),50),MID(L2,FIND("CASA ",L2),50))),(MID(L2,FIND("LOC ",L2),50))),(MID(L2,FIND("OF",L2),50))),(MID(L2,FIND("PARC",L2),50))),(MID(L2,FIND("DEPT",L2),50)))

    (2) Failed attempt to simplify:
    =MID(L2,FIND(OR("DPTO","CASA ","DEPT","LOC ","OF","PARC"),L2),50)

    Am I doomed to clunkiness? Or am I making a silly mistake?

    Thanks in advance!

    1. Hello Alex!
      For me to be able to help you better, please describe your task in more detail. Do you want to derive these values using a formula, or simply specify TRUE or FALSE, or derive some part of the cell from column L? What result should your formula return? Please let me know in more detail what you were trying to find, what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

      1. The result of the formula should be the string text starting from the position found by the FIND formula (when it finds any of the alternatives included in the OR function brackets in equation 2) and the following 50 characters (which is why the MID formula is there).

        Please let me know if you need more information!

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

          =MID(L2,SMALL(IFERROR(FIND({"DEPT","CASA","OF","PARC"},L2,1),""),1),50)

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

          1. Unfortunately it doesn't work. I had previously tried putting an array in FIND but that didn't work either. Even just =FIND({"DEPT","CASA","OF","PARC"},L2,1) returns a #VALUE error instead of a place number.

            The source string has a mix of text, numbers and blank spaces, General format, if that helps at all.

            1. Hello Alex!
              My formula works according to your explanation. Give an example of the data in cell L2 and the result you want to get. Explain what exactly the formula does not work in. Perhaps I misunderstood you. Or you didn’t say anything.
              Your FIND formula will not work, because it does not handle the event when the value is not found.

              1. OK, thank you for your time and patience!

              2. Alexander,
                Sorry to bother you again but I've discovered what it's doing (although not why - maybe you have an idea?). It's only taking the first term in the array and ignoring the rest. So with the formula as it is, it's picking out the DEPT but not CASA. If I change round the order of those terms within the array, putting CASA first and leaving DEPT second, it returns all values with CASA but not DEPT (or any of the others).
                Can you imagine what can be going on? Do I need to configure something else?
                Thanks!

              3. Hello Alex!
                My formula works correctly. Therefore, I can’t tell you the reason for the error. Try to run it on another computer. Some Excel settings might be interfering.

              4. Alexander,
                Thank you so much for your time and help. I did triple check and although the formula is correct, but it's still not working for me :/ I wonder if there's some configuration other than cell format I haven't thought about... Anyway, you've definitely helped me as much as you possibly could, and I'm very grateful for your time and patience.
                Have a lovely weekend!
                Alex

              5. OK so here's an example.
                Cell L has this: AVENIDA DEL VALLE S/N ALGARROBOS 4B CASA 21B
                Your formula: =MID(L2,SMALL(IFERROR(FIND({"DEPT","CASA","OF","PARC"},L2,1),""),1),50)
                Is giving me this: #VALUE
                When I need it to give this: CASA 21B
                i.e. return the string from the position found by the FIND formula.

              6. Hello Alex!
                I copied the formula from your post:
                =MID(L2,SMALL(IFERROR(FIND({"DEPT","CASA","OF","PARC"},L2,1),""),1),50)
                I inserted text into cell L2:
                AVENIDA DEL VALLE S/N ALGARROBOS 4B CASA 21B
                And you won’t believe it - everything works for me!
                I got the text "CASA 21B".
                I can only advise once again to check whether you wrote down the formula correctly.

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

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

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

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