Have you ever wondered how to extract keywords from titles in Google Sheets without breaking a sweat? Well, buckle up because I’m about to spill the beans on an awesome formula that will make this task a breeze!
The Magic of the Filter Formula
Believe it or not, with the help of a Filter formula, you can extract listed keywords from titles in Google Sheets. And the best part? It works like a charm! This nifty formula uses the Filter function to do all the heavy lifting for you.
But here’s the catch: the formula works best with single-word keywords. If you have multiple words in your keywords, don’t worry, I’ve got you covered. I’ll show you a handy workaround that will do the trick.
Extracting Keywords Made Easy
Let’s start with the basics. To extract keywords from titles, you need to maintain a list of keywords. This list will serve as a reference for the formula to match the keywords in the titles and extract them if they are available.
But what happens when a title contains multiple keywords? Not to worry! The formula is smart enough to extract all the keywords and separate them with a convenient comma.
In the example above, you can see how the formula works its magic. In column D, there is a list of keywords, and in column A, there are the titles. The formula in column B checks whether any of the keywords in column D are present in the titles. If a keyword is found, it is extracted and multiple keywords are separated by a comma.
How to Extract Listed Keywords from Titles in Google Sheets
Now that you’re all set, let’s dive into the formula to extract listed keywords from titles in Google Sheets. If your titles are in column A and the keyword lists are in column D, simply use the following formula in cell B2:
=proper(textjoin(", ",1,ifna(filter(split(upper(A2)," "),regexmatch(split(upper(A2)," "),textjoin("|",1,UPPER($D$2:$D)))))))
After entering the formula in cell B2, you can drag it down to cell B7 or adapt it to your data range in column A. Easy peasy, right?
The Logic Behind the Formula
Curious how this formula works its magic? Let me break it down for you step by step.
Step 1: Split Titles to Columns
First, we split the titles into separate columns using the Split function. This function splits the title into several words and converts them to uppercase. To see the result, you can enter the following formula in cell F2:
=split(upper(A2)," ")
Step 2: Join Listed Keywords for Regular Expression Match
In this step, we combine the listed keywords in column D using the Textjoin function. Enter this formula in cell F3:
=ArrayFormula(textjoin("|",1,upper($D$2:$D)))
This formula creates a regex expression that helps us extract listed keywords from titles in Google Sheets. By converting the keywords to uppercase, we avoid any issues with case sensitivity.
Step 3: Regexmatch Listed Keywords in Google Sheets
Now we use the regex expression from step 2 in the Regexmatch function. Modify the formula as follows:
=regexmatch(upper(A2),ArrayFormula(textjoin("|",1,upper($D$2:$D))))
This formula returns TRUE if any of the listed keywords find a match in cell A2, and FALSE otherwise.
Step 4: Filter Column Names (Equal to Extracting Listed Keywords from Titles)
To filter the keywords based on the matches, we combine the formulas from steps 1 and 3 using the Filter function. Here’s the generic formula:
=filter(split(upper(A2)," "),regexmatch(split(upper(A2)," "),textjoin("|",1,upper($D$2:$D))))
To join the extracted keywords with commas as separators, we wrap the above formula with the Textjoin function. The Proper function converts the uppercase keywords to proper case for readability. The use of IFNA in the formula is optional.
Extracting Multiple Words Keywords from Titles
The formula I’ve shared works like a charm for extracting single-word keywords. But what if you have multiple words in your keywords? Don’t panic! There’s a simple workaround.
Let’s say you have a multiple-word keyword like “Index Match” in your keyword list. In this case, you can simply restructure the keyword from “Index Match” to “IndexMatch”. You can use the search and replace function (Ctrl+H) to replace ” ” with ““. Don’t forget to make the same change in column A to ensure consistency.
And voila! You can now extract single and multiple-word listed keywords from titles in Google Sheets with ease.
So there you have it, my juicy secret formula for extracting listed keywords from titles in Google Sheets. Give it a try and watch your productivity soar!
For more amazing tips and tricks, head over to Crawlan.com. Happy keyword extracting!
