One of these days I am going to have to learn how to read.<g Yep! His post
sure does say there can be less than 3 leading digits. You formula is how I
would have done it had I read the post fully... except I think to match the
OP's request, shouldn't you start with LEFT(I18:I30,3), not LEFT(I18:I30,4)?

Rick


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rick,

That won't find numbers that are less than 3 numbers in length:

But using this will: it isn't flexible (in terms of maximum digits
possible) but only the OP can tell if it works. Array entered as well

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),IF(ISNUMBER(--LEFT(I18:I30,2)),--LEFT(I18:I30,2),IF(ISNUMBER(--LEFT(I18:I30,1)),--LEFT(I18:I30,1),"")))),1)

HTH,
Bernie
MS Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30
and to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3 numbers.)