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

rmtaylor

Board Regular
Joined
Feb 17, 2002
Messages
155
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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
 
Upvote 0
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?

Aladin
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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))))

Aladin
 
Upvote 0
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))))

Aladin

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:

=CHAR(MAX(CODE(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))&":"&ADDRESS(ROW(A1),COLUMN()-1)))))

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

=ADDRESS(ROW(A1),COLUMN(A1))

and G3

=ADDRESS(ROW(A1),COLUMN()-1)

which I thought would be the same thing.

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

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top