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.

With that said, I made a mistake in my original function and left out the
*MISSING* indicator. I just posted a corrected function against my original
message for the function.

--
Rick (MVP - Excel)


--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 18 Jul 2009 10:54:43 -0400, "Rick Rothstein"
wrote:

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

I note that given the following modification of the OP's test string:

"User requested authority of emergency ID for reason NHUSER1234567
Restore
of object to library LEVEL2 under remedy 61074317. 06/04/09 17:46 QPGMR"

your routine returns 1234567 whereas my UDF returns "MISSING" since there
are
no seven digit words.

(Lori's formulas return 1074317)

--ron