The easiest way to extract vowels and consonants separately in Google Sheets is by using the REGEXREPLACE function. Another option is the SUBSTITUTE function, but I find that formula a bit lengthy, so I prefer to use REGEXREPLACE.

The logic for extracting vowels and consonants is as follows:

• To extract vowels from a text string, replace all the consonants with an empty string (“”).
• To extract consonants, replace all the vowels with an empty string (“”). See the examples of how to do this in the formulas below.

Extracting Vowels from a Text String in Google Sheets

Assume cell A1 contains the English alphabet: abcdefghijklmnopqrstuvwxyz. The formula would extract the vowel letters aeiou. Note that I have provided both case-sensitive and case-insensitive formulas for this.

Case-Sensitive Formulas to Extract Vowels

• Lowercase Vowels:

=REGEXREPLACE(A1,"[^aeiou]", "")

This will return “aeiou”.

• Uppercase Vowels:

=REGEXREPLACE(A1,"[^AEIOU]", "")

This will return an empty string “”. If the alphabets in A1 are uppercase, it will return “AEIOU”.

These formulas are case-sensitive, meaning they treat lowercase and uppercase letters differently.

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. In the above example, we specified the character class for vowels. Here, instead, we will specify the consonants.

Case-Sensitive Formulas to Extract Consonants

Here, I have two separate formulas for lowercase and uppercase letters.

• Lowercase Consonants:

=REGEXREPLACE(A1, "[^b-df-hj-np-tv-z]", "")

• Uppercase Consonants:

=REGEXREPLACE(A1, "[^B-DF-HJ-NP-TV-Z]", "")

Case-Insensitive Formula to Extract Consonants

• Lower/Upper/Proper Case Consonants: This is the recommended formula for removing vowels from a string and extracting the consonants.

=REGEXREPLACE(A1, "(?i)[^b-df-hj-np-tv-z]", "")

Additional Tip: Make a Regex Formula Case-Insensitive Using the LOWER Function

You can use the LOWER or UPPER functions to make a REGEX formula case-insensitive. For example, here’s how you can extract vowels using this method:

• Vowel Extraction with Case Insensitivity:

=REGEXREPLACE(LOWER(A1),"[^aeiou]", "")

Simply wrap the cell reference with the LOWER function.

Now, for extracting consonants:

• Consonant Extraction with Case Insensitivity:

=REGEXREPLACE(LOWER(A1), "[^b-df-hj-np-tv-z]", "")

Please note that this will convert the result to lowercase letters since we are using the LOWER function. If you use UPPER instead, it will convert the result to uppercase letters. However, when using UPPER, make sure to use the uppercase character class as well.

You can visit my Google Sheets function guide to explore more REGEX functions.

Resources

• How to Autofill Alphabets in Google Sheets
• How to Only Allow the Entry of Alphanumerics in a Column in Google Sheets
• How to Properly Sort Alphanumeric Values in Google Sheets
• Max and Min Strings Based on Alphabetic Order in Google Sheets
• How to Rank Data by Alphabetical Order in Google Sheets
• Change Text Case in Google Sheets: Upper, Lower, Proper, Sentence
• Restrict or Force Text Entry to All Caps, All Lower Cases, or Proper Case in Google Sheets
• Formula to Filter Uppercase | Lowercase | Proper Case Text in Google Sheets
• Case Insensitive Unique in Google Sheets