Thanks:  0
Likes:  0

# Thread: Help on is it poss to pick the (>) letter from a row ???

1. Help
I am attempting without success to set up a sheet that will pick out the highest letter from a row or array similar to the example below

A B C D E Answer = E
A B D Answer = D
A B D F G H Answer = H
I would appreciate any help on this problem
Robert
Scotland

2. On 2002-04-11 22:58, rmtaylor wrote:
Help
I am attempting without success to set up a sheet that will pick out the highest letter from a row or array similar to the example below

A B C D E Answer = E
A B D Answer = D
A B D F G H Answer = H
I would appreciate any help on this problem
Robert
Scotland
Array-enter:

=CHAR(MAX(CODE(A2:E2)))

where each cell in A2:E2 houses a single letter.

In order to array-enter a formula, hit control+shift+enter at the same time, not just enter.

3. I attempted this by adding the formula to a sheet as a test as read no sucess???

4. On 2002-04-11 22:58, rmtaylor wrote:
Help
I am attempting without success to set up a sheet that will pick out the highest letter from a row similar to the example below

A B C D E Answer = E
A B D Answer = D
A B D F G H Answer = H
I would appreciate any help on this problem
Robert
Scotland
I attempted this by adding the formula to a sheet as a test as read no sucess???

[ This Message was edited by: rmtaylor on 2002-04-12 00:12 ]

5. On 2002-04-12 00:11, rmtaylor wrote:
On 2002-04-11 22:58, rmtaylor wrote:
Help
I am attempting without success to set up a sheet that will pick out the highest letter from a row similar to the example below

A B C D E Answer = E
A B D Answer = D
A B D F G H Answer = H
I would appreciate any help on this problem
Robert
Scotland
I attempted this by adding the formula to a sheet as a test as read no sucess???
[ This Message was edited by: rmtaylor on 2002-04-12 00:12 ]
What did you get? An error, #VALUE! maybe?

6. OK, this is a long way around it...

If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
=MID(\$B10,1,1)
=MID(\$B10,2,1)
=MID(\$B10,3,1)
=MID(\$B10,4,1)
=MID(\$B10,5,1)

Then in column G enter;
=IF(B1<>"",CODE(B1),1)
and fill right to column 'K'

then in column (L)enter;
=MAX(G1:K1)

Finally in column 'M' enter;
=CHAR(L1)
this will give you the highest letter...

7. On 2002-04-12 02:22, jimboy wrote:
OK, this is a long way around it...

If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
=MID(\$B10,1,1)
=MID(\$B10,2,1)
=MID(\$B10,3,1)
=MID(\$B10,4,1)
=MID(\$B10,5,1)

Then in column G enter;
=IF(B1<>"",CODE(B1),1)
and fill right to column 'K'

then in column (L)enter;
=MAX(G1:K1)

Finally in column 'M' enter;
=CHAR(L1)
this will give you the highest letter...

=CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
will do the same job,
BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

I'm trying to figure a way round this, hang fire.

8. On 2002-04-12 02:54, Ian Mac wrote:
On 2002-04-12 02:22, jimboy wrote:
OK, this is a long way around it...

If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
=MID(\$B10,1,1)
=MID(\$B10,2,1)
=MID(\$B10,3,1)
=MID(\$B10,4,1)
=MID(\$B10,5,1)

Then in column G enter;
=IF(B1<>"",CODE(B1),1)
and fill right to column 'K'

then in column (L)enter;
=MAX(G1:K1)

Finally in column 'M' enter;
=CHAR(L1)
this will give you the highest letter...

=CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
will do the same job,
BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

I'm trying to figure a way round this, hang fire.
Ian

I assumed a single letter per cell in my original reply and I said so. If it is just a string in a single cell and you want the "letter" with highest ASCII value, the following array-formula will do:

=CHAR(MAX(CODE(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1))))

9. On 2002-04-12 03:06, Aladin Akyurek wrote:
On 2002-04-12 02:54, Ian Mac wrote:
On 2002-04-12 02:22, jimboy wrote:
OK, this is a long way around it...

If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
=MID(\$B10,1,1)
=MID(\$B10,2,1)
=MID(\$B10,3,1)
=MID(\$B10,4,1)
=MID(\$B10,5,1)

Then in column G enter;
=IF(B1<>"",CODE(B1),1)
and fill right to column 'K'

then in column (L)enter;
=MAX(G1:K1)

Finally in column 'M' enter;
=CHAR(L1)
this will give you the highest letter...

=CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
will do the same job,
BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

I'm trying to figure a way round this, hang fire.
Ian

I assumed a single letter per cell in my original reply and I said so. If it is just a string in a single cell and you want the "letter" with highest ASCII value, the following array-formula will do:

=CHAR(MAX(CODE(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1))))

I also was considering them in single cell, I must admit I was playing with both idea and was getting close, alas! you've done it again.
What about the other 1 though (single cells!)

I've been trying to use INDIRECT() in the CODE() part:

is where I'm at. unfortunatly it doesn't work and I can't figure out why because if I use

=CHAR(MAX(CODE(INDIRECT(G2&":"&G3))))

where G2 houses

and G3

which I thought would be the same thing.

Any Ideas, is the INDIRECT having problems with the Array Formula entry?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•