Forum Discussion

Natas20075's avatar
Natas20075
Copper Contributor
Apr 17, 2019

Need Help on Formule SEARCH

Hi,

I'm a new user from Excel software and I try to make a Formule that will help me to Search a specific text and show which one found from a comment text

here the one I made for now: =IFERROR(IF(ISNUMBER(SEARCH({"P3","P2","P1"},AE50)),"P3",FALSE),"")

 

I need to find any of the "P1","P2","P3" when that is present in that kind of text:

"P3 // imprimante a releve // Gui : B00 // due 4/12/2019 10:23:00 Adresse du site : 25, Route 125, Chertsey, J0K Incident Desja : 1114" 

or

"#DESJ: 09258 / g2472 / C14 / P1 / Due: 4/6/2019 13:20:49 / Probleme avec le distributeur / DI-GA-FLM"

 

The problem I got right now, is if the P3 like in the first example is not in the first list of search, that show me an empty or False result. I need a formule  that will be able to search the P1-P2-P3 an any way and show me the one he found. It will always be only one "P#" per text. 

So to resume I need to get the P3 or P2 or P1 to be showing

 

thank you for taking the time to help me

excel
Formulas and Functions

7 Replies

  • karthickrichard's avatar
    karthickrichard
    Copper Contributor
    Apr 17, 2019

    Natas20075 You can try this formula.

     

    =IFERROR(IFS(ISNUMBER(SEARCH("P1",AE50,1)),"P1",ISNUMBER(SEARCH("P2",AE50,1)),"P2",ISNUMBER(SEARCH("P3",AE50,1)),"P3"),"FALSE")

    • Natas20075's avatar
      Natas20075
      Copper Contributor
      Apr 17, 2019
      Hi Karthickrichard,
      no that only showing False everytime, no able to see which P it is.
      • Natas20075's avatar
        Natas20075
        Copper Contributor
        Apr 17, 2019

        Thanks to both of you I think I find it:

        =IF(ISNUMBER(FIND("P3",AE7,1)),"P3",IF(ISNUMBER(FIND("P2",AE7,1)),"P2",IF(ISNUMBER(FIND("P1",AE7,1)),"P1")))

        that look and write the exact P# that found

        I really appreciate that you answer that fast

         

        have a good day ^=^

    • Natas20075's avatar
      Natas20075
      Copper Contributor
      Apr 17, 2019

      SergeiBaklan, that work fine, except is there a way I can see the one that find? for now with the formula I put ("P3", False) if that working, but can it be possible that just put P2 or P1 by Excel himself or I need to every time replace the "P3" for the one I need to see?

       

Resources