Need Help on Formule SEARCH

Copper Contributor

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

7 Replies

@Natas20075 , that is like

=IFERROR(IF(SUM(--ISNUMBER(SEARCH({"P3","P2","P1"},AE50))),"P3",FALSE),"")

 

 

@Sergei Baklan, 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?

 

@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")

Hi Karthickrichard,
no that only showing False everytime, no able to see which P it is.

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 , I'd only use SEARCH instead of FIND - the latest is case sensitive, don't think that's critical for you.

@Sergei Baklan, no exacte, I change it to "Search" anyway because I got one cell where I need to find Word like "meet" so better "Search" as is non-sensitive. but that work just fine, thank you again for your help