With the help of a Filter formula, yes you heard me right, you can extract listed keywords form titles in Google Sheets. We can use the Filter function for extracting keywords.

It works well with single-word keywords. For multiple words in keywords, you may require to follow a workaround, that also I have included in this Sheets’ tutorial.

Regarding the ‘titles’, it can post/article titles (for SEO purpose), material description containing a material name (extract item/material name from description), etc.

The easiest way to extract keywords from titles is to maintain a list of keywords. The awesome formula, which you are going to get in this tutorial, can match these keywords in the titles and extract if available.

What about multiple keywords in a single title?

If there are multiple keywords in the title, the formula would extract all the keywords and separate them with a comma.

Example to Extracting Listed Keywords from Titles in Google Sheets:

In column D, as you can see, there is already a list of keywords (listed keywords). My formulas in column B check whether any of these keywords are in the titles in column A.

If available, the formula extracts that keyword. Multiple keywords are comma-separated in the output column B.

How to Extract Listed Keywords from Titles in Google Sheets

If your titles are in column A and the keyword lists are in column D as above, use this formula to extract listed keywords in Google Sheets.

=proper(textjoin(", ",1,ifna(filter(split(upper(A2)," "),regexmatch(split(upper(A2)," "),textjoin("|",1,UPPER($D$2:$D)))))))

Apply this formula in cell B2 and then drag down to cell B7 or as per your data range in column A.

The soul of my above formula is the Filter function. Let me explain the formula part by part. Take time to follow this as it will certainly help you to modify the formula, if you want, later.

Formula Logic Behind Filtering Listed Keywords in Google Sheets

You may doubt how can one use Filter to extract matching keywords from a title. It’s possible as we are splitting the title to many columns based on space delimiter (boundary) on the title.

The filter function is applying to these columns (split texts) after matching the keyword. You will get a clear idea about this by following the below step-by-step instructions.

Step 1: Split Titles to Columns

The first title is in cell A2, right?

=split(upper(A2)," ")

The above Split function based formula splits the title to several words and also make it uppercase. To show you what this Split formula returns, I am just entering it in cell F2.

Step 2: Join Listed Keywords for Regular Expression Match

In this step, I am combining the listed keywords available in column D. For this I am using the Textjoin function.

Enter this formula in cell F3:

=ArrayFormula(textjoin("|",1,upper($D$2:$D)))

Result:

IMPORTRANGE|INDEX|QUERY|VLOOKUP

This is actually a regex expression which helps us to extract listed keywords from titles in Google Sheets.

Similar to split titles, I have changed the case of keywords to Upper to avoid case sensitivity.

You May Like: Change Text to Upper, Lower and Sentence Case.

Step 3: Regexmatch Listed Keywords in Google Sheets

Use the above regular expression in Regexmatch. Simply modify the above formula as below.

=regexmatch(upper(A2),ArrayFormula(textjoin("|",1,upper($D$2:$D))))

It will return TRUE if any of the listed keywords find a match in A2, else FALSE.

Replace upper(A2) with the Step 1 Split formula and wrap the entire formula with the ArrayFormula function. This is, I mean wrapping with ArrayFormula is because the split formula returns an array output.

=ArrayFormula(regexmatch(split(upper(A2)," "),ArrayFormula(textjoin("|",1,upper($D$2:$D)))))

You can see that two of the listed keywords are matching in the title (please see the two red arrow marks on the screenshot below).

Step 4: Filter Column Names (Equal to Extracting Listed Keywords from Titles)

Use the above formula in Filter to filter the keywords. Before that, if you want, you can remove the two array formulas in the above formula. Its not a must within Filter.

First, see the generic formula based on the Filter syntax FILTER(range, condition1)

FILTER(Step 1 formula,Step 3 formula)

Here is the original filter formula based on the Generic formula above.

=filter(split(upper(A2)," "),regexmatch(split(upper(A2)," "),textjoin("|",1,upper($D$2:$D))))

Join Extracted Keywords by Placing Comma as the Separator

The above formula will return the keywords in different columns. In my final formula, given at the beginning of this post, I have wrapped the above formula with Textjoin. That joins keywords by placing a comma separator.

The Proper function converts the uppercase keywords to proper case. You may also find the use of IFNA in my final formula which is not a must.

Extract Listed Multiple Words Keyword from Titles in Google Sheets

The formula given above will work fine to extract listed single word keywords from titles in Google Sheets.

Assume, you have a multiple word keyword “Index Match” in your keyword list in column D. In such a case you should do this workaround.

There are no changes in the formula. You may simply restructure the keyword from “Index Match” to “Index_Match”.

Search and replace (Ctrl+H) the keyword from “Index Match” to “Index_Match” in column A too.

This way you can extract single/multiple words listed keywords from titles in Google Sheets.