In Google Doc Spreadsheets you can use the REGEX functions to play around with text strings. To extract vowels or consonants, you can use the REGEXREPLACE function, not the REGEXEXTRACT.
The REGEX is not the only function in Sheets that we can use for this purpose. You can also use the SUBSTITUTE function to extract vowels/consonants. But I find that formula a little lengthy. So I am not using the SUBSTITUTE formula here in my examples.
The Logic:
To return vowels from a text string, what you want to do is to replace all the consonants with"".
Similarly, to return consonants, replace all the vowels with"". See how to do that in the below formula examples.
Google Sheets Formulas to Extract Vowels and Consonants
Extracting Vowels from a Text String in Google Sheets
Assume cell A1 contains the text “info inspired”, the formula would extract the vowel letters “ioiie”. But do note that I have case sensitive and case insensitive formulas for this.
Case Sensitive Formula to Extract Vowels
Lowercase Vowels:
=REGEXREPLACE(A1,"[^aeiou]", "")Uppercase Vowels:
=REGEXREPLACE(A1,"[^AEIOU]", "")The above formulas are case sensitive. That means it will treat lowercase and uppercase letters differently.
Then how to make the REGEXREPLACE formula a case insensitive one?
Case Insensitive Formula to Extract Vowels
Lower/Upper/Proper Case Vowels:
=REGEXREPLACE(A1,"(?i)[^aeiou]", "")
Extracting Consonants from a Text String in Google Sheets
The formula to extract consonants is almost the same as the formula for extracting vowels. You just need to remove the caret sign ^ (negate) in the formula.
Case Sensitive Formula to Extract Consonants
Here also I have two separate formulas for lower and uppercase letters.
Lowercase Consonants:
=REGEXREPLACE(A1,"[aeiou]", "")Uppercase Consonants:
=REGEXREPLACE(A1,"[AEIOU]", "")Case Insensitive Formula to Extract Consonants
Lower/Upper/Proper Case Consonants:
This is the recommended formula for removing vowels from a string and subsequently extract the consonants.
=REGEXREPLACE(A1,"(?i)[aeiou]", "")Make a Regex Formula Case Insensitive Using The Lower Function
You can use the functions LOWER/UPPER to make a REGEX formula case insensitive. As an example, I am extracting vowels using this method.
=REGEXREPLACE(LOWER(A1),"[^aeiou]", "")Just wrap the cell reference with the LOWER function. Now see the formula that extracts consonants. It’s also similar in use.
=REGEXREPLACE(LOWER(A1),"[aeiou]", "")You can go to my Google Sheets function guide to pick the REGEX functions to learn.
Is there a way to create conditional formatting for determining if the first two letters in a cell are consonants?
Hi, Julia,
The following formula will highlight the cell A2 if the first two letters in this cell are consonants.
=len(regexreplace(left(A2,2),"[aeiou]",))=2The same formula can be used for the range A2:A too. There are no changes in the formula.
Best,
Hi, thanks for this.
Any way to do it without space.
E.g. Green Mango > GrnMng
How about if I would like to get the Initials.
E.g. Green Mango > GM
Thanks!
The below formula would return the required consonants without space.
=REGEXREPLACE(A1,"(?i)[aeiou ]", "")To extract the initials, that means the first letter in each word, use the following formula.
=regexreplace(proper(A1), "[^A-Z]", "")Best,