Extract Vowels and Consonants Separately in Google Sheets

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

4 COMMENTS

    • 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]",))=2

      The same formula can be used for the range A2:A too. There are no changes in the formula.

      Best,

  1. 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,

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.