Thanks. I would probably revise this to "For X = 1 to Len(S) - 5"

LOL... yeah, Len(S) - 5 does make more sense. And, of course, you are most
welcome.

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
Thanks. I would probably revise this to "For X = 1 to Len(S) - 5"

Thanks very much for showing me how to create a UDF. My VBA skills are
limited, and I've never been sure of the specifics of how to do it.

"Rick Rothstein" wrote:

Then try this User Defined Function (UDF)...

Function FindSixDigitNumber(S As String) As Long
Dim X As Long
For X = 1 To Len(S)
If Mid(S, X, 6) Like "######" Then
FindSixDigitNumber = Mid(S, X, 6)
Exit For
End If
Next
End Function

To install it, press Alt+F11 to get into the VB editor, click
Insert/Module
from the VB editor menu bar and copy/paste the above code into the code
windows that appeared. Now, go back to your worksheet and use that
function
just like you would any built-in functions. For example,

=FindSixDigitNumber(A1)

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
"can any of those other numbers be 6 **or more** digits long" - don't
know,
but for purposes of this exercise, let's assume not.

Thanks for your help.


"Rick Rothstein" wrote:

You failed to mention in your original posting that your "text" could
contain other numbers besides the one you were looking for. It's kind
of
hard to give you the solution you want if you don't tell us all of the
parameters we need to know. Just out of curiosity, can any of those
other
numbers be 6 **or more** digits long? If so, would the number you want
to
find always be the last number in the text?

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
The 6-digit number is not the only numeric data in the cell.

For example, it might look like "9/2/2008 07:14 RNAJRED requested
authority
of RSQEMERG for reason RNAJRED RSQEMERG RBM holiday-restore file
937016. QPGMR"

I need a formula that will find the 6-digit number only.

Thanks.


"Rick Rothstein" wrote:

Since you are always looking for a 6-digit number, this formula
will
do
what
you ask...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),6)

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
I've got some cells containing text. Somewhere inside each cell
is a
6-digit
number, though not necessarily in the same location in each cell.
(A
few
cells may be missing the number.)

Is there some way using to find and extract the number using
built-in
functions? If not, how would I do it in a user-defined function?

Thanks.