Thanks again for your help. I'll give it a try.

I really ought to learn VBA myself. I keep nibbling at it, but never have
invested the effort to really learn it. This will be a good exercise for me.

"JLatham" wrote:

2nd Attempt - response was refused by the *##A!!@ system.

I need to resort to VBA code to do this by creating a User Defined Function
(UDF). Here is the code for it:

Function Get7Digits(sourceText As Range) As String
Dim tempResult As String
Dim LC As Integer
Dim DLC As Integer
Dim startAt As Integer
Const Digits = "0123456789"
Const lengthToReturn = 7
Const FailReturnValue = "" ' you can change this

tempResult = sourceText.Value
Get7Digits = FailReturnValue ' default failed return
If Len(tempResult) < lengthToReturn Then
Exit Function
End If
'look at each character in source text
For LC = 1 To Len(tempResult)
'compare to digits
startAt = 0
For DLC = 1 To Len(Digits)
If Mid(tempResult, LC, 1) = Mid(Digits, DLC, 1) Then
startAt = LC
Exit For
End If
Next
If startAt 0 Then
Exit For ' quit looking
End If
Next
'build 7 character value
If startAt 0 And _
(Len(tempResult) - startAt + 1) = lengthToReturn Then
Get7Digits = Mid(tempResult, startAt, lengthToReturn)
End If
'test if all characters to be returned are numeric
If Get7Digits < "" Then
For LC = 1 To Len(Get7Digits)
If InStr(Digits, Mid(Get7Digits, LC, 1)) = 0 Then
'found non-numeric character
Get7Digits = FailReturnValue
Exit For
End If
Next
End If
End Function

To put it into your workbook: open the workbook. Press [Alt]+[F11] to open
the VB Editor. In there, choose Insert -- Module. Copy the code above and
paste it into the module presented to you. Close the VB Editor.

To use it: it's just like any other Excel worksheet function. Put a formula
in a cell like
=Get7Digits(A1)
where A1 is the cell with the text entry. If the first digit in the string
is the start of a 7 digit numeric section, those 7 digits will be returned.
If it is an error situation, then what ever you assign to the FailReturnValue
in its definition will be returned. Right now I have that set to an empty
string with:
Const FailReturnValue = "" ' you can change this
you could change it this way
Const FailReturnValue = "No 7 Digit value found" ' you can change this



"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


"JLatham" wrote:


=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345678 9"))LEN(A1),0,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")),7))

That formula assumes your text entry is in A1. It also assumes that the
first numeric digit is the 1st digit in the 7 digit number you need to
extract. If there are any other digits ahead of that group, it will return
the wrong results.

Hope that helps. I actually found it in a posting by Mike White of the UK
on another site.

"Eric_NY" wrote:

,I'm using Outlook 2003.

I have a column containing free-format text values. (Example: "HNSMUPP
requested authority of ICIEMERG for reason HNSMUPP ICIEMERG 1026382 - RST
OF GUS, CMC FROM AFTSAVE. 03/07/09 06:51 QPGMR I5IPRD2A"). I need to find and
extract the 7-digit numeric value (in this example: 1026382) into another
cell.

Any suggestions? Can this be done with Excel built-in functions?

Thanks.