"Henrietta Horne" wrote in message
...
On Tue, 11 Jan 2011 20:18:51 -0500, Ron Rosenfeld
wrote:

On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

I need check a list of words against an alphabet (a string of
letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like
this:

etaoinsrhldcumgfpwybvkjxzq

Everybody has given you UDF's.

Here is an Excel function which will work so long as Alphabet contains
**all** of the characters that might be in any word:

Alphabet is the NAME of a cell that contains your alphabet, and any
other characters that might be in a word. You should put the
non-letters at the end of Alphabet.

eg: NAME some cell Alphabet
enter: etaoinsrhldcumgfpwybvkjxzq'/?


Then, with your word in A1,

This formula must be **array-entered**:

=MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 ),Alphabet))

I note in one of your other posts that, if you have a non-alphabetic
character, you want to return a 27. That being the case, you could
use:

=MIN(27,MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1),Alphabet)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

I've trieds array formulas once or twice and got confused. I've got my
function working now, but thanks for the suggestion. I like the UDF
because I can add coded to do other things and I don't have to type a
complicated formula in a cell.


Are you familiar with the formula auditing tools? I built a simple
worksheet with Ron's formula, then stepped through it with the "Evaluate
Formula" tool so I could understand what makes it work. Pretty neat!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)