Ron - Thanks. I'm going to try the Longre morefunc add-in.

If I use the add-in functions and then send the sheet to someone by email,
will the formulas still work? Or does the recipient also have to install the
add-in separately on his own machine?

Thanks for your help.

"Ron Rosenfeld" wrote:

On Thu, 16 Jul 2009 11:22:01 -0700, 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.

Easy to do using Regular Expressions.

The Regular Expression (Regex) would be "\b\d{7}\b" which translates into "find
a seven digit expression that is surrounded by word boundaries. You need to
assert the word boundary to avoid picking up 7 digits of a longer number that
might be there.

This can be implemented in several ways.

1. Download and install Longre's free morefunc.xll add-in (use Google to find a
functioning download site). Then use the formula:

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

2. Write a short UDF using VBA.

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 like

=Seven(A1)

in some cell.

================================
Option Explicit
Function Seven(s As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d{7}\b"
If re.test(s) = True Then
Set mc = re.Execute(s)
Seven = mc(0)
Else
Seven = "*MISSING*"
End If
End Function
=================================
--ron