How can I search for a string of numeric digits within a text stri

[Eric_NY]

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.

 

[Mike H]

Hi,

Try this

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

 

[Rick Rothstein]

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&"0123456789")),6)

 

[Gary''s Student]

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

 

[Eric_NY]

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]

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?

 

[Eric_NY]

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

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)

 

[Eric_NY]

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]

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.