MAX text

T

tonywig

I want to find the highest value in a column of text.
MAX (it appears) only deals with numeric values.

MUST be possible surely since I can sort on that column OK.
:confused:

Any help greatfully received.
 
S

Sandy Mann

If you are just talking about the first letter in the text then
=CHAR(MAX(CODE(LEFT(G1:G4,1))))

array entered (Ctrl + Shift + Enter) will return the *Max* character

I don't know if this is what you want

Regards
 
D

Domenic

Assuming that A1:A10 contains your text values...

B1, copied down:

=(COUNTIF($A$1:$A$10,">"&A1)+1)+(COUNTIF($A$1:A1,A1)-1)

C1:

=INDEX(A1:A10,MATCH(MIN(B1:B10),B1:B10,0))

Hope this helps!
 
H

Harlan Grove

Domenic wrote...
Assuming that A1:A10 contains your text values...

B1, copied down:

=(COUNTIF($A$1:$A$10,">"&A1)+1)+(COUNTIF($A$1:A1,A1)-1)

C1:

=INDEX(A1:A10,MATCH(MIN(B1:B10),B1:B10,0))
Click to expand...
....

Ancillary cells not needed. This can be done with a single nonarray
formula.

=LOOKUP(2,1/(COUNTIF(A1:A10,">"&A1:A10)=0),A1:A10)
 
D

Domenic

Nice one! Thanks Harlan!

Harlan Grove said:
Domenic wrote...
...

Ancillary cells not needed. This can be done with a single nonarray
formula.

=LOOKUP(2,1/(COUNTIF(A1:A10,">"&A1:A10)=0),A1:A10)
Click to expand...
 
G

Gord Dibben

tony

What do you mean by "highest value" when it comes to text?

Text has no value AFAICT.

Do you mean "which cell has the most text"?

=MAX(LEN(A1:A10)) entered with CTRL + SHIFT + ENTER


Gord Dibben Excel MVP
 
H

Harlan Grove

Gord Dibben wrote...
Text has no value AFAICT.
Click to expand...
....

If it can be sorted in a well-defined way (and it can), it at least has
ordinal values, so MAX = last *DOES* have a well-defined meaning.
 
Top