Are you tired of manually extracting multiple words from a cell in Google Sheets? Well, worry no more! I’m here to spill the beans on how you can use the powerful REGEXEXTRACT function to do just that. So grab your favorite beverage, sit back, and let’s dive into this juicy topic!
Why Extracting Multiple Words is a Game-Changer
Before we jump into the nitty-gritty details, let me explain why extracting multiple words from a cell can be a game-changer for you. The possibilities are endless! Whether you’re analyzing data, organizing information, or simply searching for specific keywords, REGEXEXTRACT has got you covered.
Imagine this scenario: You have a cell containing a list of country names, and you want to extract certain countries from that list. With the magic of REGEXEXTRACT, you can effortlessly pluck out the countries you need. It’s like having your own personal assistant in Google Sheets!
The REGEXEXTRACT Function: Your Key to Success
So, how does REGEXEXTRACT work its magic? The syntax is simple yet powerful. You just need to remember this formula:
REGEXEXTRACT(text, regular_expression)
Let’s break it down with an example. Suppose you have a cell (let’s call it A1) that contains the following country list:
India, United States, United Kingdom, Canada, Philippines, Australia, France, Brazil, Netherlands, Malaysia, Germany, Indonesia, Pakistan, South Africa, Spain, Singapore, Italy, Mexico, Sweden, Peru, Israel, Hong Kong, Vietnam, Bangladesh, Nigeria
Now, let’s say you want to extract the country name “Hong Kong.” All you have to do is use this formula:
=REGEXEXTRACT(A1,"(?i)Hong Kong")
And just like that, REGEXEXTRACT will work its magic and fetch “Hong Kong” for you. But wait, there’s more! We can take it up a notch by adding a logical test.
Putting REGEXEXTRACT to the Ultimate Test
We all know that life isn’t perfect, and sometimes our beloved REGEXEXTRACT may return a #NA error when the desired word isn’t found. But fear not! We have a solution for that too. By combining REGEXEXTRACT with the IFERROR function, we can make our formula fail-safe.
Here’s an example:
=IFERROR(REGEXEXTRACT(A1,"(?i)Hong Kong"),"Not Available")
Now, if “Hong Kong” is found in cell A1, you’ll see “Hong Kong” as the result. But if it’s not found, you’ll see “Not Available” instead. Voila! Our formula is now foolproof.
Extracting Multiple Words: A Two-Pronged Approach
Now that you’re an expert at extracting a single word, let’s level up and tackle the art of extracting multiple words. We have two approaches for this: the nested REGEXEXTRACT formula and the LAMBDA-based formula. Let’s explore both options!
Approach 1: Nested REGEXEXTRACT Formula
The nested REGEXEXTRACT formula is perfect when you only need to extract a small number of words (2-3). Here’s an example of how it works:
=TRANSPOSE( IFERROR( { REGEXEXTRACT(A1,"(?i)Singapore"), REGEXEXTRACT(A1,"(?i)Italy"), REGEXEXTRACT(A1,"(?i)Brazil") } ) )
This formula will extract the words “Singapore,” “Italy,” and “Brazil” from cell A1. If any of the words aren’t found, you’ll see a blank cell in the result.
To remove blank cells and limit the number of values returned, you can use the QUERY function like this:
=QUERY( TRANSPOSE( IFERROR( { REGEXEXTRACT(A1,"(?i)Singapore"), REGEXEXTRACT(A1,"(?i)Italy"), REGEXEXTRACT(A1,"(?i)Brazil") } ) ), "Select * where Col1<>' ' Limit 3" )
In the above formula, the LIMIT clause controls the number of items returned (3 in this case).
Approach 2: LAMBDA-Based REGEXEXTRACT Formula
If you need to extract a large number of matching words, the nested REGEXEXTRACT formula might become too complex to manage. Enter the LAMBDA-based formula, which is flexible and efficient.
Here’s an example of how to use the LAMBDA-based formula:
=LET( range,A1, value,{"Singapore","Italy","Brazil"}, TOCOL( MAP( TOCOL(value,1), LAMBDA(r,REGEXEXTRACT(TEXTJOIN(" ",TRUE,range),"(?i)"&r)) ),3 ) )
In this formula:
- Cell A1 contains the list of words to extract.
- The array {“Singapore”, “Italy”, “Brazil”} contains the countries to match in the list.
To match more values, simply add them to the array. You can even use a cell range instead of a single cell for more flexibility.
And there you have it! With the LAMBDA-based formula, you can extract multiple matching words without breaking a sweat.
Conclusion
Congratulations, my fellow Sheets ninja! You’ve mastered the art of extracting multiple words using REGEXEXTRACT. Now you can impress your colleagues, save time, and unleash the full potential of your data analysis.
Remember, if you’re craving more Google Sheets tips, make sure to visit Crawlan.com, your go-to source for all things Sheets-related. Happy extracting!
