SOLVED

Need help with a formula

New Contributor

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:

Splash1950_0-1660117225072.png

 

This is my info that I need to fix:

Splash1950_1-1660117269855.png

 

And in the end I need formula to work like this: 

Splash1950_2-1660117429728.png

 

 

4 Replies
best response confirmed by Splash1950 (New Contributor)
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".

Riny_van_Eekelen_0-1660119284350.png

 

@Splash1950 

 

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

 

NikolinoDE

I know I don't know anything (Socrates)

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

Harun24HR_0-1660122486128.png

 

 

Thank you so much