Home

Need Help on Formule SEARCH

%3CLINGO-SUB%20id%3D%22lingo-sub-465438%22%20slang%3D%22en-US%22%3ENeed%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-465438%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20a%20new%20user%20from%20Excel%20software%20and%20I%20try%20to%20make%20a%20Formule%20that%20will%20help%20me%20to%20Search%20a%20specific%20text%20and%20show%20which%20one%20found%20from%20a%20comment%20text%3C%2FP%3E%3CP%3Ehere%20the%20one%20I%20made%20for%20now%3A%26nbsp%3B%3DIFERROR(IF(ISNUMBER(SEARCH(%7B%22P3%22%2C%22P2%22%2C%22P1%22%7D%2CAE50))%2C%22P3%22%2CFALSE)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20find%20any%20of%20the%20%22P1%22%2C%22P2%22%2C%22P3%22%20when%20that%20is%20present%20in%20that%20kind%20of%20text%3A%3C%2FP%3E%3CP%3E%22P3%20%2F%2F%20imprimante%20a%20releve%20%2F%2F%20Gui%20%3A%20B00%20%2F%2F%20due%204%2F12%2F2019%2010%3A23%3A00%20Adresse%20du%20site%20%3A%2025%2C%20Route%20125%2C%20Chertsey%2C%20J0K%20Incident%20Desja%20%3A%201114%22%26nbsp%3B%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E%22%23DESJ%3A%2009258%20%2F%20g2472%20%2F%20C14%20%2F%20P1%20%2F%20Due%3A%204%2F6%2F2019%2013%3A20%3A49%20%2F%20Probleme%20avec%20le%20distributeur%20%2F%20DI-GA-FLM%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I%20got%20right%20now%2C%20is%20if%20the%20P3%20like%20in%20the%20first%20example%20is%20not%20in%20the%20first%20list%20of%20search%2C%20that%20show%20me%20an%20empty%20or%20False%20result.%20I%20need%20a%20formule%26nbsp%3B%20that%20will%20be%20able%20to%20search%20the%20P1-P2-P3%20an%20any%20way%20and%20show%20me%20the%20one%20he%20found.%20It%20will%20always%20be%20only%20one%20%22P%23%22%20per%20text.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20to%20resume%20I%20need%20to%20get%20the%20P3%20or%20P2%20or%20P1%20to%20be%20showing%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20taking%20the%20time%20to%20help%20me%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-465438%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-465645%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-465645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322589%22%20target%3D%22_blank%22%3E%40Natas20075%3C%2FA%3E%26nbsp%3B%2C%20that%20is%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(IF(%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3ESUM(--%3C%2FSTRONG%3E%3C%2FFONT%3EISNUMBER(SEARCH(%7B%22P3%22%2C%22P2%22%2C%22P1%22%7D%2CAE50)))%2C%22P3%22%2CFALSE)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-465846%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-465846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20that%20work%20fine%2C%20except%20is%20there%20a%20way%20I%20can%20see%20the%20one%20that%20find%3F%20for%20now%20with%20the%20formula%20I%20put%20(%22P3%22%2C%20False)%20if%20that%20working%2C%20but%20can%20it%20be%20possible%20that%20just%20put%20P2%20or%20P1%20by%20Excel%20himself%20or%20I%20need%20to%20every%20time%20replace%20the%20%22P3%22%20for%20the%20one%20I%20need%20to%20see%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-466219%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-466219%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322589%22%20target%3D%22_blank%22%3E%40Natas20075%3C%2FA%3E%26nbsp%3BYou%20can%20try%20this%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(IFS(ISNUMBER(SEARCH(%22P1%22%2CAE50%2C1))%2C%22P1%22%2CISNUMBER(SEARCH(%22P2%22%2CAE50%2C1))%2C%22P2%22%2CISNUMBER(SEARCH(%22P3%22%2CAE50%2C1))%2C%22P3%22)%2C%22FALSE%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-466350%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-466350%22%20slang%3D%22en-US%22%3EHi%20Karthickrichard%2C%3CBR%20%2F%3Eno%20that%20only%20showing%20False%20everytime%2C%20no%20able%20to%20see%20which%20P%20it%20is.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-466719%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-466719%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20to%20both%20of%20you%20I%20think%20I%20find%20it%3A%3C%2FP%3E%3CP%3E%3DIF(ISNUMBER(FIND(%22P3%22%2CAE7%2C1))%2C%22P3%22%2CIF(ISNUMBER(FIND(%22P2%22%2CAE7%2C1))%2C%22P2%22%2CIF(ISNUMBER(FIND(%22P1%22%2CAE7%2C1))%2C%22P1%22)))%3C%2FP%3E%3CP%3Ethat%20look%20and%20write%20the%20exact%20P%23%20that%20found%3C%2FP%3E%3CP%3EI%20really%20appreciate%20that%20you%20answer%20that%20fast%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehave%20a%20good%20day%20%5E%3D%5E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467705%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322589%22%20target%3D%22_blank%22%3E%40Natas20075%3C%2FA%3E%26nbsp%3B%2C%20I'd%20only%20use%20SEARCH%20instead%20of%20FIND%20-%20the%20latest%20is%20case%20sensitive%2C%20don't%20think%20that's%20critical%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-468992%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20on%20Formule%20SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20no%20exacte%2C%20I%20change%20it%20to%20%22Search%22%20anyway%20because%20I%20got%20one%20cell%20where%20I%20need%20to%20find%20Word%20like%20%22meet%22%20so%20better%20%22Search%22%20as%20is%20non-sensitive.%20but%20that%20work%20just%20fine%2C%20thank%20you%20again%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Natas20075
Occasional 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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies