How to find min value in Excel text cells?

  • Thread starter Thread starter Jeff Kantner
  • Start date Start date
J

Jeff Kantner

Suppose I've got 5 cells, containing values 'b', 'd', 'a', 'e', 'c' ... if
asked to pick the minimum value, you'd say 'a' (setting aside collating
sequence issues). How do I create an Excel formula to figure out the answer
is 'a'? All the min/max functions I can find expect numbers, not text. And
I'm not in a situation where I can sort and pick top/bottom. Ideas?
 
Assuming source range is fully populated with only single letters of the same
case (ie either all are in lower case, or in upper case), here's one possible
play to get the "minimum" letter

Put in say B1, array-enter the formula, ie press CTRL+SHIFT+ENTER to confirm
the formula (instead of just pressing ENTER):
=INDEX(A1:A5,MATCH(MIN(CODE(A1:A5)),CODE(A1:A5),0))

If source range may contain a mix of upper and lower case single letters,
and case sensitivity is to be ignored, then you could try this, array-entered:
=INDEX(A1:A5,MATCH(MIN(CODE(UPPER(A1:A5))),CODE(UPPER(A1:A5)),0))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 
Try this *array* formula:

=INDEX(A1:A5,MATCH(MIN(CODE(UPPER(A1:A5))),CODE(UPPER(A1:A5)),))
 
Hi,

Here is an array function to do this:

=INDEX(A1:A5,MATCH(0,COUNTIF(A1:A5,"<"&A1:A5),))

To make it an array press Shift+Ctrl+Enter to enter it.

Since the same range is used 3 times a range name like R would simplify this
to

=INDEX(R,MATCH(0,COUNTIF(R,"<"&R),))
 
Back
Top