Max in a list of alphabets/texts

[rarmn]

Hello,

I have a list in increasing order of value

A,B,C,...,Z,AA,AB,AC,...,AZ,BA,BB,...,BZ (so on)

I want to find the maximum in this list. max should be "BZ" and minimum should be "A" in the above list.

Any suggestions.

Thanks.

 

[Robert Mika]

Hello,

I have a list in increasing order of value

A,B,C,...,Z,AA,AB,AC,...,AZ,BA,BB,...,BZ (so on)

I want to find the maximum in this list. max should be "BZ" and minimum should be "A" in the above list.

Any suggestions.

Thanks.

Is this in column or a cell?
Are there only 1-2 letters values?

 

[rarmn]

Is this in column or a cell?
Are there only 1-2 letters values?

There are only one and two letters. The list is in a row. I want the maximum letter from the list.
for eg. A < B < C ....< Z < AA < AB ....< AZ < BA ...

Thanks.

 

[Robert Mika]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		A	A	AA	AB	AC	AZ	C	G	AF
2
3			AZ
Sheet1

Cell Formulas
Range	Formula
C3	{=INDEX($A$1:$J$1,1,MATCH(LARGE(IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),1),IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[raj2206]

Hey, Thanks for writing!!

You can use the following formula:

=INDEX(A1:A9,MATCH(MAX(CODE(A1:A9)+
IFERROR(CODE(MID(A1:A9,2,1)),0)),CODE(A1:A9)+IFERROR(CODE(MID(A1:A9,2,1)),0),0))

Please change the referencing as per the requirement, in my case the values were in A1:A9.
Make sure you press Control+Shift+Enter while entering the formula, since its an array operation.

Hope this will work.

Thanks/Raj

 

[Peter_SSs]

I have a list in increasing order of value

A,B,C,...,Z,AA,AB,AC,...,AZ,BA,BB,...,BZ (so on)

There are only one and two letters. The list is in a row.

In that case, try ..

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	A	D	E	V	AB	AC	AJ	AK
3
4	AK
rarmn

 

[raj2206]

Peter, you made this awfully simple Nice!

 

[Robert Mika]

In that case, try ..

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	A	D	E	V	AB	AC	AJ	AK
3
4	AK
rarmn

Would this work for not sorted range?

 

[Peter_SSs]

Would this work for not sorted range?

No, it would always return the right-most entry.
But the OP did state that the list is in order of increasing "size", confirmed by both the examples provided and that's what I based my response on.

 

[rarmn]

Excel 2010

	A	B	C	D	E	F	G	H	I	J
1		A	A	AA	AB	AC	AZ	C	G	AF
2
3			AZ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C3 {=INDEX($A$1:$J$1,1,MATCH(LARGE(IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),1),IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you for the formula. But when i have "AZ" and "ZA" in the list if "AZ" occurs first the formula will pick "AZ" because the code value is 155 for both "AZ" and "ZA". Any suggestions. Thank you.