SOLVED

New Contributor

# Need help with a formula

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:

4 Replies
best response confirmed by Splash1950 (New Contributor)
Solution

# Re: Need help with a formula

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

# Re: Need help with a formula

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

# Re: Need help with a formula

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

# Re: Need help with a formula

Thank you so much