Forum Discussion

RP2024's avatar
RP2024
Copper Contributor
Mar 27, 2024

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.

 

 

Goal: Remove all words from each cell except those that are explicitly chosen. For example I want to be able to choose words such as "Assistant" or "Associate", etc. and have the rest of the words removed.  Here is an example of what I am looking for:

 

Thank you in advance for the advice. 

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    RP2024 

     


    Goal: Remove all words from each cell except those that are explicitly chosen. For example I want to be able to choose words such as "Assistant" or "Associate", etc. and have the rest of the words removed.  Here is an example of what I am looking for:

     


    So, it is really "looking for key words and if found put them next to the cell".

     

     

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      =IFERROR(LOOKUP(2,1/SEARCH(list_of_keywords,A1),list_of_keywords),"")

       

      • RP2024's avatar
        RP2024
        Copper Contributor

        Detlef_Lewin 

         

        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?

Resources