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.

Hi,

Try this

=LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1)))))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike



"Eric_NY" wrote:

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.

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.

Try this UDF:

Function numit(r As Range) As Double
Dim s As String, s2 As String, c As String
s2 = ""
s = r.Value
l = Len(s)
k = 0
For i = 1 To l
c = Mid(s, i, 1)
If c Like "#" Then
s2 = s2 & c
k = k + 1
If k = 6 Then Exit For
Else
s2 = ""
k = 0
End If
Next
numit = --s2
End Function

so if A1 contains:
a12dfg476492hfsjfsfhwkfhkdsh6823643274632

then

=numit(A1) will display 476492
the 12 is ignored as is the junk at the end
--
Gary''s Student - gsnu200828


"Eric_NY" wrote:

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.

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.

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.

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

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.

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.

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.