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.


Any help greatfully received.


--
tonywig


------------------------------------------------------------------------
tonywig's Profile: http://www.excelforum.com/member.php...o&userid=18985
View this thread: http://www.excelforum.com/showthread...hreadid=379061

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

"tonywig" wrote in
message ...

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.


Any help greatfully received.


--
tonywig


------------------------------------------------------------------------
tonywig's Profile:
http://www.excelforum.com/member.php...o&userid=18985
View this thread: http://www.excelforum.com/showthread...hreadid=379061

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

B1, copied down:

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

C1:

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

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=379061

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))
....

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

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

Nice one! Thanks Harlan!

In article . com,
"Harlan Grove" wrote:

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))
...

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

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

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

On Tue, 14 Jun 2005 11:36:38 -0500, tonywig
wrote:


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.


Any help greatfully received.

Gord Dibben wrote...
Text has no value AFAICT.
....

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.

Thank you all for your suggestions.
I went with Harlan's.

Much obliged.


--
tonywig


------------------------------------------------------------------------
tonywig's Profile: http://www.excelforum.com/member.php...o&userid=18985
View this thread: http://www.excelforum.com/showthread...hreadid=379061