Forum Discussion
How do I delete all content except for some words?
Hello. I am looking to clean up a data set and doing so manually is taking a lot of time. The issue: A column of data contains a string of words in random arrangement. Please see below. ...
=IFERROR(LOOKUP(2,1/SEARCH(list_of_keywords,A1),list_of_keywords),"")
Thank you for reframing the question! I am having some difficulty in making the formula work. As a specific example, let's say I am looking for words "Assistant" and "Professor". The data is column starts in A1 and I want the result to be placed in the adjacent column, B1. However I want the key words appear in a specific order i.e. "Assistant Professor" and not "Professor Assistant" even if the latter was the manner in which the data appeared in column A. Given the above, could you please edit the formula accordingly?
As variant
=BYROW(texts, LAMBDA(txt, LET( positions, IFERROR(SEARCH(keywords,txt),0), order, SORTBY( keywords, positions), found, FILTER(order, SORT(positions)), IFERROR(TEXTJOIN(" ",, found), "") ) ) )for
- Thank you, Sergei! Much appreciated.
RP2024 , you are welcome
=IFERROR(TEXTJOIN(" ",TRUE,CHOOSECOLS(SORT(FILTER(keywords_table,ISNUMBER(SEARCH(list_of_keywords,A1))),2),1)),"")]keywords_table includes the list_of_keywords and a number indicating the order.
- Thank you very much! Huge time saving.
