MAX text
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. -- 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. :confused: 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. :confused: 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 |
All times are GMT +1. The time now is 08:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com