Min/Max of Numbers as Text

[Todd]

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

 

[Jacob Skaria]

Try the below array formula. you press CTRL+SHIFT+ENTER to enter the formula.
If successful in 'Formula Bar' you can notice the curly braces at both ends
like "{=<formula>}"

=MIN(IF(A1:A10<>"",VALUE(A1:A10)))

If this post helps click Yes

 

[Max]

Try something like this ..
array-entered (ie press CTRL+SHIFT+ENTER to confirm the formula):
=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Teethless mama]

=TEXT(MIN(INDEX(A1:A5+0,)),"00000")

Just press ENTER

 

[Todd]

This is exactly what I was looking for. Thank you.

 

[Todd]

Your formula did the trick. However, another posting had a slightly more
simplified formula that achieves the same result (see below).

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")

Thanks so much!

 

[Todd]

Your formula comes very close except it returns the result as a value and I
need to keep the result text. Another response gave me the formulas below
that keep the result as text. Thought I would share it with you. Thank you
very much for your help.

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")

 

[Teethless mama]

=TEXT(MIN(A1:A5+0),"00000")

=TEXT(MAX(A1:A5+0),"00000")

Those formula are required CTRL+SHIFT+Enter, my formula just press ENTER.

 

[Todd]

Thanks for pointing that out. I didn't catch that. I never know how many
codes will be in column B so I changed your formula to be as shown below and
it works great (plus it's a "regular" formula because few people in my area
understand array formulas). Thanks again.

=TEXT(MIN(INDEX(INDIRECT("B2:B"&COUNTA(B2:B65536)+1)+0,)),"00000")