brilliant Rick thanks!!
is there an easy way to add to that to return the entire contents of the
cell containing the smallest number?

"Rick Rothstein (MVP - VB)" wrote:

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