On Wed, 27 May 2009 08:43:02 -0700, Eric_NY
wrote:

Thank you.

I hate to ask for programming and debugging help, but since I don't fully
understand the formula, I'm afraid I have no choice.

I've discovered I have some error values in my data, which I didn't describe
in my original post. In particular, I have some values which, due to input
errors, do not contain the 7-digit numeric string. Your current formula
handles them this way:

For example, "HNRAMUS requested authority of ICIEMERG for reason HNRAMUS
ICIEMERG 'to fix OTPROC issue for P0CALC. 03/12/09 23:08 QPGMR" generates
"0CALC."

What I'd like is to return a zero in those cases (or some other special
value) - not a #N/A or some other error value.

Thanks again. I'm grateful for your help.

Eric

One way of handling this would be to download and install Longre's free
morefunc.xll add-in (Google for a working download site), then use this
formula:

=REGEX.MID(A1,"\b\d{7}\b")

If there is no "stand-alone" 7 digit substring, it will return a blank.

If you cannot find the add-in, you could use a UDF:

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula

=RegexMid(A1,"\b\d{7}\b")

in some cell.

==============================
Option Explicit
Function RegexMid(S As String, sPattern As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
If re.test(S) = True Then
Set mc = re.Execute(S)
RegexMid = mc(0).Value
End If
End Function
=========================
--ron