to separate the vowels and the consonants of a word.

[gencoglu27]

Hi
I need to separate the vowels of the words listed in Col A and write them in Col B and the consonants in Col C ?

as in . "ice cream" ----"ieea" ,"ccrm"
"ground" ----" ou" ,"grnd"

thanks,

[dangelor]

Here's a formula to remove the vowels...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",""),"e",""),"I",""),"o",""),"u","")

and the consonants...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"b",""),"c",""),"d",""),"f",""),"g",""),"h",""),"j",""),"k",""),"l",""),"m",""),"n",""),"p",""),"q",""),"r",""),"s",""),"t",""),"v",""),"w",""),"x",""),"y",""),"z","")

[Richard Buttrey]

Or with a couple of VBA functions..

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Armando Montes]

Another one
http://www.mrexcel.com/forum/showthread.php?t=243823

Please Login or Register  to view this content.

=Letters(A1,0) will return consonants
=Letters(A1,1) will return vowels

[DonkeyOte]

FWIW, you could remove the need for the repeat upper/lower by using IgnoreCase within the RegExp