In Google Sheets, there are various functions available to manipulate text strings. One interesting use case is extracting vowels or consonants from a given text. In this article, we will explore how to achieve this using REGEX functions in Google Sheets.
Extracting Vowels from a Text String in Google Sheets
To extract vowels from a text string, we can use the REGEXREPLACE function. Let’s assume we have the text “info inspired” in cell A1. The formula to extract the vowel letters “ioiie” would be:
=REGEXREPLACE(A1,"[^aeiou]", "")
If you want the formula to be case insensitive, treating lowercase and uppercase vowels equally, you can use the following formula:
=REGEXREPLACE(A1,"(?i)[^aeiou]", "")
Extracting Consonants from a Text String in Google Sheets
The formula to extract consonants from a text string is similar to the one for extracting vowels. You just need to remove the caret sign “^” (which negates) in the formula. For example:
=REGEXREPLACE(A1,"[aeiou]", "")
To make the formula case insensitive, you can use:
=REGEXREPLACE(A1,"(?i)[aeiou]", "")
Alternatively, you can also use the LOWER or UPPER functions to achieve case insensitivity. For example, to extract vowels, you can use:
=REGEXREPLACE(LOWER(A1),"[^aeiou]", "")
And to extract consonants, you can use:
=REGEXREPLACE(LOWER(A1),"[aeiou]", "")
By wrapping the cell reference with the LOWER function, you can make the formula case insensitive.
These are just a few examples of how you can use REGEX functions in Google Sheets to extract vowels and consonants from text strings. If you want to dive deeper into REGEX functions and learn more about their usage, visit Crawlan.com for a comprehensive guide on Google Sheets functions.
Remember, with the power of Google Sheets, manipulating and extracting specific characters from text strings has never been easier. Happy exploring!
