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.

Is there any hard and fast rule that governs where the number might appear,
such as before the first full stop?

--
__________________________________
HTH

Bob

"Eric_NY" wrote in message
...
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.

No, none that I can see. This is free-format input by the user, and as much
as we'd like to insist that the user use a prescribed format, we can't be
sure that they always will. I just need a way to look for a 7-digit number
anywhere within the text.

Thanks for any suggestions you can provide.

"Bob Phillips" wrote:

Is there any hard and fast rule that governs where the number might appear,
such as before the first full stop?

--
__________________________________
HTH

Bob

"Eric_NY" wrote in message
...
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.

Always the same number??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric_NY" wrote in message
...
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.

Unfortunately no.

"Don Guillett" wrote:


Always the same number??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Eric_NY" wrote in message
...
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.

If the word "remedy" and a single space always precede the number, and if
the number is truly always 7 digits, you could use the following (A1
contains the original text):

=IF(ISNUMBER(FIND("remedy",A1)), MID(A1,FIND("remedy",A1)+7,7), "missing")

Otherwise, it probably will be easier to write a user-defined function in
VB. However, the rules for finding the desired number must be precise. For
example, is there always only one 7-digit number preceded by a space? Or is
there always specific set of words ("remedy" and others) preceding the
number?

Basically, how would a human distinguish and find the number if he had to
scan the text left to right or right to left character by character or word
by word, where a "word" is a sequence of non-space characters.


----- original message -----

"Eric_NY" wrote in message
...
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.

Unfortunately there's no precise format. I just need to look for a 7-digit
number, which can be anywhere in the string. (I could restrict it to looking
for the first 7-digit number, since I know that there's never more than one.)

Maybe it's time for me finally to force myself to learn VB. I'm a former
programmer, but never learned VB.

Thanks for your suggestions.

"JoeU2004" wrote:

If the word "remedy" and a single space always precede the number, and if
the number is truly always 7 digits, you could use the following (A1
contains the original text):

=IF(ISNUMBER(FIND("remedy",A1)), MID(A1,FIND("remedy",A1)+7,7), "missing")

Otherwise, it probably will be easier to write a user-defined function in
VB. However, the rules for finding the desired number must be precise. For
example, is there always only one 7-digit number preceded by a space? Or is
there always specific set of words ("remedy" and others) preceding the
number?

Basically, how would a human distinguish and find the number if he had to
scan the text left to right or right to left character by character or word
by word, where a "word" is a sequence of non-space characters.


----- original message -----

"Eric_NY" wrote in message
...
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.

Is the number always precede by a space when the number is interior to the
text? What about that "dot" after it... is there always a dot following it?
If the dot might not always be there, is there always a space after the
number when it is interior to the text?

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
Unfortunately there's no precise format. I just need to look for a 7-digit
number, which can be anywhere in the string. (I could restrict it to
looking
for the first 7-digit number, since I know that there's never more than
one.)

Maybe it's time for me finally to force myself to learn VB. I'm a former
programmer, but never learned VB.

Thanks for your suggestions.

"JoeU2004" wrote:

If the word "remedy" and a single space always precede the number, and if
the number is truly always 7 digits, you could use the following (A1
contains the original text):

=IF(ISNUMBER(FIND("remedy",A1)), MID(A1,FIND("remedy",A1)+7,7),
"missing")

Otherwise, it probably will be easier to write a user-defined function in
VB. However, the rules for finding the desired number must be precise.
For
example, is there always only one 7-digit number preceded by a space? Or
is
there always specific set of words ("remedy" and others) preceding the
number?

Basically, how would a human distinguish and find the number if he had to
scan the text left to right or right to left character by character or
word
by word, where a "word" is a sequence of non-space characters.


----- original message -----

"Eric_NY" wrote in message
...
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.

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

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

On Thu, 16 Jul 2009 12:04:04 -0700, Eric_NY
wrote:

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.

Ordinarily no. They would have to install it themselves. However, with the
morefunc add-in there is an option, I believe it is a menu option, to install
the add-in as part of the workbook. If you do that, it will then be usable by
the recipient with no particular effort on his part.

One caution concerning the add-in -- it will not work on strings that are
longer than 255 characters. This, apparently is an .xll limitation, and there
is no good way around it within the add-in.

By the way, if you should use the UDF approach, the UDF should be embedded
within the workbook, so its use should be transparent to your user. But there
are a lot of other useful functions in morefunc.
--ron

there is an option, I believe it is a menu option,
to install the add-in as part of the workbook.

This option is currently not available for Excel 2007. In other versions of
Excel, when the add-in is installed it should create a new item in the Tools
menu, ToolsMorefuncEmbed Morefunc in the workbook. Note that embeding
Morefunc will add some size to the file. According to Morefunc help, about
500kb. 350kb for the functions and 150kb for the help file.

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Thu, 16 Jul 2009 12:04:04 -0700, Eric_NY

wrote:

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.

Ordinarily no. They would have to install it themselves. However, with
the
morefunc add-in there is an option, I believe it is a menu option, to
install
the add-in as part of the workbook. If you do that, it will then be
usable by
the recipient with no particular effort on his part.

One caution concerning the add-in -- it will not work on strings that are
longer than 255 characters. This, apparently is an .xll limitation, and
there
is no good way around it within the add-in.

By the way, if you should use the UDF approach, the UDF should be embedded
within the workbook, so its use should be transparent to your user. But
there
are a lot of other useful functions in morefunc.
--ron

On Thu, 16 Jul 2009 17:12:03 -0400, "T. Valko" wrote:

This option is currently not available for Excel 2007.

Good point. If the OP is using Excel 2007, the UDF solution might be better.
--ron

"However, with the morefunc add-in there is an option, I believe it is a menu
option, to install the add-in as part of the workbook. "

I can't find that option. Where should I look for it? Which menu?

"Ron Rosenfeld" wrote:

On Thu, 16 Jul 2009 12:04:04 -0700, Eric_NY
wrote:

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.

Ordinarily no. They would have to install it themselves. However, with the
morefunc add-in there is an option, I believe it is a menu option, to install
the add-in as part of the workbook. If you do that, it will then be usable by
the recipient with no particular effort on his part.

One caution concerning the add-in -- it will not work on strings that are
longer than 255 characters. This, apparently is an .xll limitation, and there
is no good way around it within the add-in.

By the way, if you should use the UDF approach, the UDF should be embedded
within the workbook, so its use should be transparent to your user. But there
are a lot of other useful functions in morefunc.
--ron

On Fri, 17 Jul 2009 15:28:01 -0700, Eric_NY
wrote:

"However, with the morefunc add-in there is an option, I believe it is a menu
option, to install the add-in as part of the workbook. "

I can't find that option. Where should I look for it? Which menu?

Which version of Excel are you using? If you are using 2007+, that option is
no longer available. If you are using an earlier version of Excel, I believe
that, when you install morefunc, you need to make a selection to enable this;
and then, if I recall correctly, it shows up on the Tools menu.

If you are using Excel 2007+, and don't want to require your users to install
morefunc, I would suggest using the UDF regex approach I previously posted.
--ron

I would still be interested in the answer to my questions...

"Is the number always precede by a space when the number
is interior to the text? What about that "dot" after it... is there
always a dot following it? If the dot might not always be
there, is there always a space after the number when it is
interior to the text?"

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
"However, with the morefunc add-in there is an option, I believe it is a
menu
option, to install the add-in as part of the workbook. "

I can't find that option. Where should I look for it? Which menu?

"Ron Rosenfeld" wrote:

On Thu, 16 Jul 2009 12:04:04 -0700, Eric_NY

wrote:

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.

Ordinarily no. They would have to install it themselves. However, with
the
morefunc add-in there is an option, I believe it is a menu option, to
install
the add-in as part of the workbook. If you do that, it will then be
usable by
the recipient with no particular effort on his part.

One caution concerning the add-in -- it will not work on strings that are
longer than 255 characters. This, apparently is an .xll limitation, and
there
is no good way around it within the add-in.

By the way, if you should use the UDF approach, the UDF should be
embedded
within the workbook, so its use should be transparent to your user. But
there
are a lot of other useful functions in morefunc.
--ron

For a formula approach maybe try this (for text in A1):

=MIN(MMULT(10^{6,5,4,3,2,1,0},--TEXT(MID(A1,COLUMN(A:IV)+{0;1;2;3;4;5;6},1),"0;;0; \1\e\9")))

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

I should have added to ignore any answer greater than 7 digits, this could be
done by wrapping the formula below in =TEXT( ... ,"[<1e9]0;""MISSING""")

"Lori" wrote:

For a formula approach maybe try this (for text in A1):

=MIN(MMULT(10^{6,5,4,3,2,1,0},--TEXT(MID(A1,COLUMN(A:IV)+{0;1;2;3;4;5;6},1),"0;;0; \1\e\9")))

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

I know you asked for a formula, but would a UDF (user defined function) be
acceptable (it would require allowing macros to run)? If so, press Alt+F11
to go into the VB editor and, once there, add a Module (Insert/Module from
its menu bar). Next copy/paste the following into the code window that
opened up...

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

Now, go back to your worksheet and use this formula in whatever cell you
want (changing the A1 reference to the cell address containing your text)...

=First7DigitNumber(A1)

This UDF finds the first "isolated" 7 digit number (that is, a 7 digit
number at the beginning or end of the text or, if interior to the text, with
non-digit characters in front and behind it).

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
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.

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

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

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)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

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

My last message had the wrong opening sentence (it was meant for another
message I was working on). This is what I meant to post...

The function I posted does not work as I had indicated... it finds the last
7-digit number, not the first. Here is the corrected code (plus I added the
missing *MISSING* indicator)...

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)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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...

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)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

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

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

I found this definition of a word boundary in Regular Expressions...

"A word boundary represents the spot where a letter or
number meets a space, apostrophe, a period, or anything
else that isn't a letter or number"

Given that, this modification of my function should do what your RegExp
solution does...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!a-zA-Z0-9]#######[!a-zA-Z0-9]" Then
First7DigitNumber = Mid(S, X, 7)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

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

On Sat, 18 Jul 2009 12:33:47 -0400, "Rick Rothstein"
wrote:

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.

Yes, I did.


I found this definition of a word boundary in Regular Expressions...

"A word boundary represents the spot where a letter or
number meets a space, apostrophe, a period, or anything
else that isn't a letter or number"

Not quite what I understand it to be (but close)./

The definitions I've seen indicate that a word boundary "Matches at the
position between a word character (anything matched by \w) and a non-word
character (anything matched by [^\w] or \W) as well as at the start and/or end
of the string if the first and/or last characters in the string are word
characters."

And, at least in VBScript, a word character is a digit, letter or underscore
e.g: [A-Za-z0-9_]



Given that, this modification of my function should do what your RegExp
solution does...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!a-zA-Z0-9]#######[!a-zA-Z0-9]" Then
First7DigitNumber = Mid(S, X, 7)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

It comes pretty close. Just change this line to include the underscores:

If Mid(" " & S & " ", X, 9) Like "[!a-zA-Z0-9_]#######[!a-zA-Z0-9_]" Then

--ron

I had the damnedest time trying to find what RegExp considered a word
boundary (basically, each sight just kept say use \w without listing what it
consider the boundary). When I finally found the one I cited, I figured it
was a universal definition. Now I'm guessing there might be version
differences between the various RegExp engines. Yes, I fix to account for
the underbar is as you have shown it. For the archives, here is the UDF with
the change you indicated...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!a-zA-Z0-9_]#######[!a-zA-Z0-9_]"
Then
First7DigitNumber = Mid(S, X, 7)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 18 Jul 2009 12:33:47 -0400, "Rick Rothstein"
wrote:

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.

Yes, I did.


I found this definition of a word boundary in Regular Expressions...

"A word boundary represents the spot where a letter or
number meets a space, apostrophe, a period, or anything
else that isn't a letter or number"

Not quite what I understand it to be (but close)./

The definitions I've seen indicate that a word boundary "Matches at the
position between a word character (anything matched by \w) and a non-word
character (anything matched by [^\w] or \W) as well as at the start and/or
end
of the string if the first and/or last characters in the string are word
characters."

And, at least in VBScript, a word character is a digit, letter or
underscore
e.g: [A-Za-z0-9_]



Given that, this modification of my function should do what your RegExp
solution does...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!a-zA-Z0-9]#######[!a-zA-Z0-9]"
Then
First7DigitNumber = Mid(S, X, 7)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

It comes pretty close. Just change this line to include the underscores:

If Mid(" " & S & " ", X, 9) Like "[!a-zA-Z0-9_]#######[!a-zA-Z0-9_]" Then

--ron

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I know you asked for a formula, but would a UDF (user defined function) be
acceptable (it would require allowing macros to run)? If so, press Alt+F11
to go into the VB editor and, once there, add a Module (Insert/Module from
its menu bar). Next copy/paste the following into the code window that
opened up...

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

Now, go back to your worksheet and use this formula in whatever cell you
want (changing the A1 reference to the cell address containing your
text)...

=First7DigitNumber(A1)

This UDF finds the first "isolated" 7 digit number (that is, a 7 digit
number at the beginning or end of the text or, if interior to the text,
with non-digit characters in front and behind it).

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
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.

Ignore this message... it was mis-sent.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I know you asked for a formula, but would a UDF (user defined function) be
acceptable (it would require allowing macros to run)? If so, press Alt+F11
to go into the VB editor and, once there, add a Module (Insert/Module from
its menu bar). Next copy/paste the following into the code window that
opened up...

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

Now, go back to your worksheet and use this formula in whatever cell you
want (changing the A1 reference to the cell address containing your
text)...

=First7DigitNumber(A1)

This UDF finds the first "isolated" 7 digit number (that is, a 7 digit
number at the beginning or end of the text or, if interior to the text,
with non-digit characters in front and behind it).

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
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.