Find text in free-format text field

[Eric_NY]

,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.

 

[JLatham]

=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))>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

 

[Eric_NY]

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

 

[Ron Rosenfeld]

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

 

[JLatham]

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]

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.

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

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