I am still not sure if the OP wanted the 7-digit number to stand alone (as a
"word") or not, so I just went for the first isolated 7 digits in a row
whether imbedded in other text or not. The reason I thought that is because
of the "dot" that followed the 7-digit number in the OP's posted example
text. In thinking about it, I'm guessing you took that to be a period at the
end of a sentence.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I know you asked for a formula, but would a UDF (user defined function) be
acceptable (it would require allowing macros to run)? If so, press Alt+F11
to go into the VB editor and, once there, add a Module (Insert/Module from
its menu bar). Next copy/paste the following into the code window that
opened up...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function

Now, go back to your worksheet and use this formula in whatever cell you
want (changing the A1 reference to the cell address containing your
text)...

=First7DigitNumber(A1)

This UDF finds the first "isolated" 7 digit number (that is, a 7 digit
number at the beginning or end of the text or, if interior to the text,
with non-digit characters in front and behind it).

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
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.