How to Extract Multiple Words Using Regexextract in Google Sheets

0
92
Extract Multiple Words Using Regexextract

We can extract multiple words in a cell into a row or column in Google Sheets. We can use a REGEXEXTRACT formula for this purpose. So learn the simple steps to extract multiple words using REGEXEXTRACT formula in Google Doc Spreadsheets.

I think it’s not necessary to explain why such type of text extraction is required as it purely depends your job. For me, I want to search a cell which contains a list of country names and extract few countries if existing in the list. These are the names of some of the top countries where this blog, Info Inspired, has a good presence.

extract multiple matching words in a cell

Here with the REGEXTEXTRACT formula I am extracting all the matching words in a sentence, or comma separated list, in a cell in google sheets. Let’s learn this trick.

Steps to Extract Multiple Words Using Regexextract in Google Sheets

You can extract a single word in a cell by using the REGEXEXTRACT as below. Suppose Cell A1 has the above same country list and I just want to extract the country name “Hong Kong”. How to do this with a REGEX formula in Google Sheets? Here it’s.

=regexextract(A1,”Hong Kong”)

But just don’t limit here. You can make this Regex formula to behave like a logical test. That means you can test cell A1 for “Hong Kong” and return the word if available or else return another value. This type of test is a must otherwise Regexextract would return “#NA” error. Here is that formula where we are making use of the IFERROR function.

=iferror(regexextract(A1,”Honag Kong”),”Not Available”)

So always remember to use Iferror function together with Regex in such scenarios. Now let us go back to our topic how to extract multiple words using Regexextract in Google Sheets.

For multiple words, you can tune the above formula as below. This is the same formula above but multiple Regexextract that separated by comma and an open clause Curly Braces.

=iferror({REGEXEXTRACT(A1,”Singapore”),REGEXEXTRACT(A1,”Italy”),REGEXEXTRACT(A1,”Brazil”)})

Here we have to use Curly Braces as the result would expand to three cells as we are looking to extract three words. The result would be like this.

result showing multiple regex extract as an array

If the formula found any mismatch, there would be a blank cell in the result. For example, suppose “Italy” is not in cell A, Column D would be blank.

In our main formula (please refer the first screenshot), I’ve just used TRANSPOSE to return the output into a column. You can further expand the formula to remove the blank cell by using Query as below and also limit the number of words to be returned.

=query(transpose(iferror({REGEXEXTRACT(A1,”Singapore”),REGEXEXTRACT(A1,”Italy”),REGEXEXTRACT(A1,”Brazil”)})),”Select * where Col1<>” Limit 3″)

Here the limit clause in the last part controls the number of words to be returned. Hope you could follow this tutorial. Thanks for the stay and enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here