Extract Vowels and Consonants Separately in Google Sheets

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

Case Insensitive Formula to Extract Vowels in Google Sheets

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.

4 COMMENTS

Julia Marcotte September 29, 2019 At 7:51 am

Is there a way to create conditional formatting for determining if the first two letters in a cell are consonants?

- Prashanth September 29, 2019 At 8:39 am
  
  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,
  
ehmi June 26, 2019 At 1:43 pm

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!

- Prashanth June 27, 2019 At 8:42 am
  
  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,