Vowels and Consonants

bevbevvybbev

New Member
Joined
Dec 25, 2003
Messages
7
Ok, i am writing some stuff to extract different types of word info from rather large tables of words

For instance, all the countries in the world

I need a formula to take vowels from England so you get one column EA
I need a formula to take consonants from England so you get one column NGLND

I then need to have a column for each letter showing which letters are in which word with a number by multiples

Anyway, the vowel/consanant thing seems to be stupidly difficult

Help!

Thanks in advance

Happy New Year

Bev
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Man, this is really hard

I thought just extracting the vowels would be easy! It was easy with the consonants, just by nestling 6 SUBSTITUTES and replacing with blanks, I figured that bit out...


But doing that in reverse is a pain

ALSO i've never run macros before and although I know how to program it seems a bit wierd in Excel...I guess I'll get used to it!

thanks, more help still appreciated!
 
Upvote 0
Hi Bev:

In the following macro based approach I have made a couple of assumptions -- 1. that all your entries are in UpperCase; and 2. the letter Y as a Vowel ...
y031225h2.xls
CDEF
1VowelsConsonants
2ENGLANDEANGLND
3AUSTRALIAAUAIASTRL
4EGYPTEYGPT
5RUSSIAUIARSS
6EXCELEEXCL
7MICROSOFTIOOMCRSFT
8BEVERLEYEEEYBVRL
Sheet2a


I used the followind code that should be installed in a general module ...
Code:
Sub yExtractVowelsAndConsonants()
    For Each cell In Intersect(ActiveSheet.UsedRange, Columns("c"))
        For j = 1 To Len(cell)
            If Asc(Mid(cell, j, 1)) = 65 Or Asc(Mid(cell, j, 1)) = 69 Or _
                Asc(Mid(cell, j, 1)) = 73 Or Asc(Mid(cell, j, 1)) = 79 Or _
                Asc(Mid(cell, j, 1)) = 85 Or Asc(Mid(cell, j, 1)) = 89 Then
                yletV = yletV & Mid(cell, j, 1): cell.Offset(0, 1) = yletV
                Else: yletC = yletC & Mid(cell, j, 1): cell.Offset(0, 2) = yletC
            End If
        Next
        yletV = ""
        yletC = ""
    Next
End Sub
 
Upvote 0
Thanks

Perhaps I'm being particularly dumb here, but I'm getting syntax errors like crazy by copying that code into the Macro editor
 
Upvote 0
See my post in http://www.mrexcel.com/board2/viewtopic.php?t=52283&start=10 Put the functions in there in a standard module. Now, suppose your country names are in column A starting with A15. Then, in B15 enter =RegExpSubstitute(A15,"[^aeiouAEIOU]","") and in C15 =RegExpSubstitute(A15,"[aeiouAEIOU]","")

Column B will contain the vowels, C the consonants.
bevbevvybbev said:
Man, this is really hard

I thought just extracting the vowels would be easy! It was easy with the consonants, just by nestling 6 SUBSTITUTES and replacing with blanks, I figured that bit out...


But doing that in reverse is a pain

ALSO i've never run macros before and although I know how to program it seems a bit wierd in Excel...I guess I'll get used to it!

thanks, more help still appreciated!
 
Upvote 0
Hi Bev:

The following formula based approach may be of some interest to you --
y031225h2.xls
ABCDE
1WordVowelsCosonants
2EENGLANDEANGLND
3AAUSTRALIAAIUSTRL
4IEGYPTEYGYPT
5ORUSSIAAIURSS
6UEXCELEXCL
7YMICROSOFTIOMCRSFT
8BEVERLEYEYBVRLY
Sheet2c


The formula in cell D2 is ...

=MCONCAT(IF(ISNUMBER(FIND($A$2:$A$7,C2)),MID(C2,FIND($A$2:$A$7,C2),1),""))

this is an array formula and uses the MCONCAT function from MoreFunc Add-in

The formula in cell E2 is ...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"A",""),"E",""),"I",""),"O",""),"U","")

One caveat here -- you will notice I have produced only one copy of a vowel in a word. Please post back whether this is acceptable or not.
 
Upvote 0
bevbevvybbev said:
Thanks

Perhaps I'm being particularly dumb here, but I'm getting syntax errors like crazy by copying that code into the Macro editor
Hi Bev:

Where did you install the code -- the code I posted goes in the general module. This what you can do

1. copy the code on the clipboard
2. from within the EXCEL workbook you are working on, press AL+F11, that should take you to the VB Editor
3. In the Project Window, select the wokbook you are working on; insert a Module
4. Paste the code in the large white space to the right
5. Return to the Workbook -- activate the worksheet you are working on and run the macro.

I hope this helps.
 
Upvote 0
Thanks for all your help, learning curve in steep incline but very cool!

I think I tracked down the 'add in' you were talking about and installed it, it seems to be there

however, putting

=MCONCAT(IF(ISNUMBER(FIND($A$2:$A$7,C2)),MID(C2,FIND($A$2:$A$7,C2),1),""))

into a cell just gives me #NAME? - again, I could have done something silly

Obviously the cells in mine are in different places, I get what you are doing with the array idea though

At least I've leaned that that is a cool add in, what an add in is etc

To answer your question unfortunately I was looking to have ALL the vowels show up such as EEIE or IEOE etc

Why the hell I'm doing this on xmas day is anyones guess!! (or you for that matter)

Thanks again from UK

Bev
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top