help : convert text to number then split it to vocal and consonant number

[kiralukaslarson]

hello
i want convert every letter written in B1 to Number
the letter is
A, J, S to number 1
B, K, T to number 2
C, L, U to number 3
D, M V to number 4
E, N, W to number 5
F.O,X to number 6
G, P, Y to number 7
H,Q, J to number 8
I, R to number 9

after that the result is split to vocal and consonant number
the consonan letter goes to B2 and Vocal goes to B3
and all letter written in B1 goes to B4
ex :

[/IMG]

thank u

 

[Danerida]

Well this was a bit of a challenge.

Formulas:

Cell B2  =SUM(SUM(((MID(B1,ROW($1:$25),1)={"j","s"})*1)),SUM((MID(B1,ROW(1:25),1)={"b","k","t"})*2),SUM((MID(B1,ROW(1:25),1)={"c","l"})*3),SUM((MID(B1,ROW(1:25),1)={"d","m","v"})*4),SUM((MID(B1,ROW(1:25),1)={"n","w"})*5),SUM((MID(B1,ROW(1:25),1)={"f","x"})*6),SUM((MID(B1,ROW(1:25),1)={"g","p","y"})*7),SUM((MID(B1,ROW(1:25),1)={"h","q","z"})*8),SUM((MID(B1,ROW(1:25),1)={"r"})*9))

Cell B3  =SUM(SUM(((MID(B1,ROW($1:$25),1)="a")*1)),SUM((MID(B1,ROW(1:25),1)="u")*3),SUM((MID(B1,ROW(1:25),1)="e")*5),SUM((MID(B1,ROW(1:25),1)="o")*6),SUM((MID(B1,ROW(1:25),1)="i")*9))

Cell B4  =SUM(SUM(((MID(B1,ROW($1:$25),1)={"a","j","s"})*1)),SUM((MID(B1,ROW(1:25),1)={"b","k","t"})*2),SUM((MID(B1,ROW(1:25),1)={"c","l","u"})*3),SUM((MID(B1,ROW(1:25),1)={"d","m","v"})*4),SUM((MID(B1,ROW(1:25),1)={"e","n","w"})*5),SUM((MID(B1,ROW(1:25),1)={"f","o","x"})*6),SUM((MID(B1,ROW(1:25),1)={"g","p","y"})*7),SUM((MID(B1,ROW(1:25),1)={"h","q","z"})*8),SUM((MID(B1,ROW(1:25),1)={"i","r"})*9))

All of these formulas are ARRAY formulas and need to be confirmed using CONTROL+SHIFT+ENTER. When you have done this correctly, Excel will insert {} around the formula.

Dan

 

[kiralukaslarson]

Well this was a bit of a challenge.

Formulas:

Cell B2  =SUM(SUM(((MID(B1,ROW($1:$25),1)={"j","s"})*1)),SUM((MID(B1,ROW(1:25),1)={"b","k","t"})*2),SUM((MID(B1,ROW(1:25),1)={"c","l"})*3),SUM((MID(B1,ROW(1:25),1)={"d","m","v"})*4),SUM((MID(B1,ROW(1:25),1)={"n","w"})*5),SUM((MID(B1,ROW(1:25),1)={"f","x"})*6),SUM((MID(B1,ROW(1:25),1)={"g","p","y"})*7),SUM((MID(B1,ROW(1:25),1)={"h","q","z"})*8),SUM((MID(B1,ROW(1:25),1)={"r"})*9))

Cell B3  =SUM(SUM(((MID(B1,ROW($1:$25),1)="a")*1)),SUM((MID(B1,ROW(1:25),1)="u")*3),SUM((MID(B1,ROW(1:25),1)="e")*5),SUM((MID(B1,ROW(1:25),1)="o")*6),SUM((MID(B1,ROW(1:25),1)="i")*9))

Cell B4  =SUM(SUM(((MID(B1,ROW($1:$25),1)={"a","j","s"})*1)),SUM((MID(B1,ROW(1:25),1)={"b","k","t"})*2),SUM((MID(B1,ROW(1:25),1)={"c","l","u"})*3),SUM((MID(B1,ROW(1:25),1)={"d","m","v"})*4),SUM((MID(B1,ROW(1:25),1)={"e","n","w"})*5),SUM((MID(B1,ROW(1:25),1)={"f","o","x"})*6),SUM((MID(B1,ROW(1:25),1)={"g","p","y"})*7),SUM((MID(B1,ROW(1:25),1)={"h","q","z"})*8),SUM((MID(B1,ROW(1:25),1)={"i","r"})*9))

All of these formulas are ARRAY formulas and need to be confirmed using CONTROL+SHIFT+ENTER. When you have done this correctly, Excel will insert {} around the formula.

Dan

thank u but formula in cell B3 didnt work
should i edit formula

 

[Danerida]

In what way did it not work? Does it give a result or none at all? Are you sure you entered it as an ARRAY formula (as described in my first post)? More information or a sample workbook would help me to help you.
Dan

 

[Rick Rothstein]

i want convert every letter written in B1 to Number
the letter is
A, J, S to number 1
B, K, T to number 2
C, L, U to number 3
D, M V to number 4
E, N, W to number 5
F.O,X to number 6
G, P, Y to number 7
H,Q, J to number 8
I, R to number 9

The letter J appears twice in your table... should the last one have been "Z" maybe?

after that the result is split to vocal and consonant number
the consonan letter goes to B2 and Vocal goes to B3
and all letter written in B1 goes to B4

When you say "vocal", do you mean "vowel"?

 

[Danerida]

The letter J appears twice in your table... should the last one have been "Z" maybe?
When you say "vocal", do you mean "vowel"?

Those were both my assumptions in the formulas provided.

 

[Rick Rothstein]

Those were both my assumptions in the formulas provided.

In that case, I have a more compact formula for cell B4 than the one you posted. Normally enter this one...

=SUMPRODUCT(1+MOD(CODE(MID(SUBSTITUTE(UPPER(B1)," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B1," ","")))),1))-65,9))

Also note that this formula does not set an artificial maximum number of characters of 25 like your formula does.

 

[VBA Geek]

This will work for words where you might have "strange" characters as well, like # or @ or ^ etc...

=SUMPRODUCT(MOD(CODE(MID(UPPER(B1),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(B1))),1))-65,9)+1,--ISNUMBER(SEARCH(MID(UPPER(B1),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(B1))),1),"BCDFGHJKLMNPQRSTVWXYZAEIOU")))


only vowels:
=SUMPRODUCT(MOD(CODE(MID(UPPER(B1),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(B1))),1))-65,9)+1,--ISNUMBER(SEARCH(MID(UPPER(B1),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(B1))),1),"AEIOU")))

only consonants:
=SUMPRODUCT(MOD(CODE(MID(UPPER(B1),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(B1))),1))-65,9)+1,--ISNUMBER(SEARCH(MID(UPPER(B1),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(B1))),1),"BCDFGHJKLMNPQRSTVWXYZ")))

 

[kiralukaslarson]

The letter J appears twice in your table... should the last one have been "Z" maybe?



When you say "vocal", do you mean "vowel"?

ups sorry Z is the right one
typo
yup vowel AIUEO