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

 

[Yogi Anand]

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

Hi Bev:

If you are getting a #NAME? error , some thing in your use of my formula is not being recognized.

To make sure that there are no syntax errors (errors in formula names, range names, ...), just try to use the same data that I have using the same cells that I have used, using the same formulas that I have used and see if you can get the same results -- if you do then you can start playing further along with your data, in different cells, and so forth.

 

[bevbevvybbev]

Just did as you said and still gives me that #NAME? error

The MoreFunc AddIn is installed

The formula is in the formula box as you wrote it

groan

What have i done wrong[/img]

 

[Aladin Akyurek]

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

...

You need to activate the add-in via Tools|Add-Ins, otherwise you'll get a #NAME? error.

BTW, it is not difficult to adapt the formulas from the link Just_Jon quoted...

Book3
	A	B	C	D
1	Item	Consonants	Vowels
2	ENGLAND	NGLND	EA
3	RUSSIA	RSS	UIA
4	FRANCE	FRNC	AE
5	ITALY	TL	IAY
6	SPAIN	SPN	AI
Sheet1

The formulas are...

B2:

=MCONCAT(IF(ISNUMBER(MATCH(SETV(MID(A2,INTVECTOR(LEN(A2),1,1),1)),{"E","A","I","O","U","Y"},0)),"",GETV()))

which gives you the consonants.

C2:

=MCONCAT(IF(ISNUMBER(MATCH(SETV(MID(A2,INTVECTOR(LEN(A2),1,1),1)),{"E","A","I","O","U","Y"},0)),GETV(),""))

which gives you the vowels.

These formulas must be confirmed with control+shift+enter instead of just enter.

I'd like to add that the code for RegExp looks good. It would be nice indeed if Longre would want to add that baroque bit from the Unix world to his morefunc.

 

[bevbevvybbev]

OKOK

I'm on a mac running OSX

Perhaps that makes a difference

For instance, I have a dll file which came with the add in I have no idea where to put it!!!

 

[Aladin Akyurek]

OKOK

I'm on a mac running OSX

Perhaps that makes a difference

For instance, I have a dll file which came with the add in I have no idea where to put it!!!

Alas. Morefunc add-in is not available for the Mac. I'd suggest using the RexExp code.

 

[tusharm]

I wonder if scripting through VBScript or JScript (implemented in vbscript.dll) is available on the Mac. I wouldn't be surprised if it isn't.

OKOK

I'm on a mac running OSX

Perhaps that makes a difference

For instance, I have a dll file which came with the add in I have no idea where to put it!!!

Alas. Morefunc add-in is not available for the Mac. I'd suggest using the RexExp code.

 

[Aladin Akyurek]

I wonder if scripting through VBScript or JScript (implemented in vbscript.dll) is available on the Mac. I wouldn't be surprised if it isn't...

If that's not possible on the Mac, the following functions can be substituted for MCONCAT and for the SETV/GETV pair.

Function aconcat(a As Variant, Optional sep As String = "") As String 
' Harlan Grove, Mar 2002 
Dim y As Variant 

If TypeOf a Is Range Then 
For Each y In a.Cells 
aconcat = aconcat & y.Value & sep 
Next y 
ElseIf IsArray(a) Then 
For Each y In a 
aconcat = aconcat & y & sep 
Next y 
Else 
aconcat = aconcat & a & sep 
End If 

aconcat = Left(aconcat, Len(aconcat) - Len(sep)) 
End Function

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

To add the foregoing bits of code to your workbook:

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy both code bits above and paste the copy in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.


Now we can have:

aaExtractConsonantsAndVowels bevbevvybbev.xls
	A	B	C	D
1	Item	Len	Consonants	Vowels
2	ENGLAND	7	NGLND	EA
3	RUSSIA	6	RSS	UIA
4	FRANCE	6	FRNC	AE
5	ITALY	5	TL	IAY
6	SPAIN	5	SPN	AI
aconcat

B2:

=LEN(A2)

C2:

=ACONCAT(IF(ISNUMBER(MATCH(V(MID(A2,ROW(INDIRECT("1:"&B2)),1)),{"E","A","I","O","U","Y"},0)),"",V()))

D2:

=ACONCAT(IF(ISNUMBER(MATCH(V(MID(A2,ROW(INDIRECT("1:"&B2)),1)),{"E","A","I","O","U","Y"},0)),V(),""))

 

[bevbevvybbev]

Success!!

I was forgetting to use { and }

can someone tell me what they're for?

Also, when you're trying to format a lot of cells with the same formula which use {}, how do you get it to work? It kept removing them in the 'calculate' box.

One other thing - does Excel let you make a bunch of cells all uppercase or lowercase or do you have to script that?

Thanks for your help guys....now I've got to work out how to create a table of all the letters of the alphabet with each letter of each word appearing in the appropriate column with a number next to it showing how many there were ie A2 in the A column

that is after I've had some xmas turkey

thanks

Bev

 

[Aladin Akyurek]

Success!!

I was forgetting to use { and }

can someone tell me what they're for?

Also, when you're trying to format a lot of cells with the same formula which use {}, how do you get it to work? It kept removing them in the 'calculate' box.

I guess you mean the formula with ACONCAT... It's a so-caleed array or matrix formula (see the Help file for an intro to them). You need to confirm such formulas with control+shift+enter instead of just with enter to instruct Excel accordingly. Excel itself puts braces around them when properly confirmed.

Once it's appropriately conformed on a cell, you can copy down the formula. So there is no need to repeat the entry process.

One other thing - does Excel let you make a bunch of cells all uppercase or lowercase or do you have to script that?...

Have a look at UPPER, LOWER, an PROPER worksheet functions. If you want to do this thru VBA, do a search at this site for code.