So, to correct the formula you'd just need to add a length test

Or, use Harlan's formula where he expands it to use the MID function.

Biff

"T. Valko" wrote in message
...
That formula is more or less designed to be used as an argument for
extracting a number from a string. But, it can be used as is with a little
tweak.

The formula is concatenating the digits to the string. So, if:

A1 = XX

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

This portion:

A1&"0123456789"

Evaluates to:

XX0123456789

The 0 being in position 3

Basically, the expression: A1&"0123456789" is used as an error trap. So,
to correct the formula you'd just need to add a length test:

=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345678 9"))LEN(A1),"",MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")))

Biff

"Dave F" wrote in message
oups.com...
This formula doesn't seem to work correctly. If I have in cell A2 the
text XX the number 3 is returned. But there's no number in that
string of characters.

What am I missing?

The array formula proposed by the OP, however, reliably returns #N/A
if there is no number in the cell.

Dave

On May 10, 1:51 pm, Harlan Grove wrote:
"Peo Sjoblom" wrote...
This formula by Domenic is much more efficient

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" ))

...

FWIW, Aladin Arkyurek had posted a variant using FIND two months
before Domenic posted his using SEARCH, and there's an old Japanese
language post (3 years before Aladin's and Domenic's) showing this
exact idiom in the Google Groups archive.

Almost every Excel question has been asked and answered several times
before.