Extracting consecutive numbers from cell

**Canuckle777** · 11-20-2013, 01:04 PM

Good morning,

I am getting close but so far away from this. I have about 60,000 emails to go through and extract a service order number so I can cross reference if it exists in a database. Here is what I am looking at for the cells A1 - A11:

HTML Code:

The formula I am using to extract the 7 digit number is below:

=LOOKUP(9.9E+307,1*LEFT(MID(A13,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A13&"0123456789")),999),ROW($1:$999)))

Here are the results I get from the extraction formula that resides in B1 - B11:

HTML Code:

For the most part it works but there are some that end up like the third result. The issue is, I would like it to be able to find 6 or 7 consecutive digits in a row (some earlier service orders are 6 digit). Am I on the right track?

I appreciate any help you can offer.

Best regards,

D

**martindwilson** · 11-20-2013, 04:29 PM

the reason you get 41482
is it finds 7-27 and sees that as 27/2/2013 41482 is the date code for that
how you find number strings of a given length is a different matter
providing your 2010 is 32 bit down load and activate the morefunc add in

note from mrexcel forum you may have to do this
After you have installed it on your computer, open Excel, go to the developer tab on the ribbon, click on the add-ins button and browse to where it was installed (probably C:\Program Files\MoreFunc). Add references to both Morefunc.xll and Morefunc12.xlam (do this one at a time). From there you should be up and running...

http://en.kioskea.net/download/downl...d-in-for-excel
then you can use
=REGEX.MID(A1,"\b(\d{6,7})\b")
or try this udf use as
=extractstring(A1)

Function extractstring(s As String) As String
Static rx As Object
Dim rxMatches As Object
 
If rx Is Nothing Then
    Set rx = CreateObject("VBScript.RegExp")
    With rx
        .Pattern = "\b(\d{6,7})\b"
        .Global = True
        .IgnoreCase = True
    End With
End If
 
With rx
    Set rxMatches = .Execute(s)
    If rxMatches.Count > 0 Then extractstring = rxMatches(rxMatches.Count - 1).Value
End With
End Function