Aug 10 2022 12:44 AM
Hey amigos
I need your help. I'm trying to write a formula for my excel document. I have a sheet with a lot of rows in which is a lot of unnecessary information. I need formula to find in those cells a specific range of word and delete everything till that word, how can I do this? I will add some pics how I need that formula to work :))
This is my range of word that I need to find in sentence:
This is my info that I need to fix:
And in the end I need formula to work like this:
Aug 10 2022 01:14 AM
Solution@Splash1950 The formula in the attached workbook might work for you. Note that the list of words to search for is in a named range called "keywords".
Aug 10 2022 01:18 AM
=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,100)
How To Extract Last Two Words From Text Strings In Excel?
a none Microsoft site
Hope I could help you with these information / links.
I know I don't know anything (Socrates)
Aug 10 2022 02:08 AM
@Splash1950 For dynamic array approach you can try BYROW() function. See the attached file.
=BYROW(A1:A5,LAMBDA(a,IFERROR(TRIM(LET(x,FILTER($L$1:$L$5,ISNUMBER(SEARCH($L$1:$L$5,a))),MID(a,SEARCH(x,a),1000))),"")))
Non array approach.
=IFERROR(TRIM(LET(x,FILTER($L$1:$L$5,ISNUMBER(SEARCH($L$1:$L$5,A1))),MID(A1,SEARCH(x,A1),1000))),"")
Aug 10 2022 01:14 AM
Solution@Splash1950 The formula in the attached workbook might work for you. Note that the list of words to search for is in a named range called "keywords".