I should have added to ignore any answer greater than 7 digits, this could be
done by wrapping the formula below in =TEXT( ... ,"[<1e9]0;""MISSING""")

"Lori" wrote:

For a formula approach maybe try this (for text in A1):

=MIN(MMULT(10^{6,5,4,3,2,1,0},--TEXT(MID(A1,COLUMN(A:IV)+{0;1;2;3;4;5;6},1),"0;;0; \1\e\9")))

"Eric_NY" wrote:

I have a column containing text values like "User requested authority of
emergency ID for reason NHUSER23 Restore of object to library LEVEL2
under remedy 1074317. 06/04/09 17:46 QPGMR".

Some of the cells contain a 7-digit number and others don't. The 7-digit
number does not start at a fixed location in the text.

Is it possible to write a formula which looks for the 7-digit number, and
returns the number if present, and another value (such as 0 or "*MISSING*")
if not?

I'm using Excel 2003.

Thanks.