Vowels and Consonants

[bevbevvybbev]

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

[Tana Lee]

Aloha...

See if the posts here are of any help:

http://www.mrexcel.com/board2/viewtopic.php?t=72768

Tana-Lee

[just_jon]

See -- http://216.92.17.166/board2/viewtopi...r=asc&start=10

[bevbevvybbev]

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!

[Yogi Anand]

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

******** ******************** ************************************************************************>

Microsoft Excel - y031225h2.xls ___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C1 =
	C	D	E	F
1	*	Vowels	Consonants	*
2	ENGLAND	EA	NGLND	*
3	AUSTRALIA	AUAIA	STRL	*
4	EGYPT	EY	GPT	*
5	RUSSIA	UIA	RSS	*
6	EXCEL	EE	XCL	*
7	MICROSOFT	IOO	MCRSFT	*
8	BEVERLEY	EEEY	BVRL	*
Sheet2a *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I used the followind code that should be installed in a general module ...

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

[bevbevvybbev]

Thanks

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

[tusharm]

See my post in http://www.mrexcel.com/board2/viewto...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.

Originally Posted by bevbevvybbev

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!

[Yogi Anand]

Hi Bev:

The following formula based approach may be of some interest to you --

******** ******************** ************************************************************************>

Microsoft Excel - y031225h2.xls ___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
D2E2D3E3D4E4D5E5D6E6D7E7D8E8 =
	A	B	C	D	E
1	*	*	Word	Vowels	Cosonants
2	E	*	ENGLAND	EA	NGLND
3	A	*	AUSTRALIA	AIU	STRL
4	I	*	EGYPT	EY	GYPT
5	O	*	RUSSIA	AIU	RSS
6	U	*	EXCEL	E	XCL
7	Y	*	MICROSOFT	IO	MCRSFT
8	*	*	BEVERLEY	EY	BVRLY
Sheet2c *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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.

[Yogi Anand]

Originally Posted by bevbevvybbev

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.

[bevbevvybbev]

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