Try this UDF:
Function pattsubtr(wholestring)
   partstring = ""
   For i = 3 To Len(wholestring) - 6
       If Val(Mid(wholestring, i, 7)) > 0 Then
           partstring = Mid(wholestring, i - 2, 9)
           Exit For
       End If
   Next i
   pattsubtr = partstring
End Function

There is one limitation: NNNNNNN cannot be 0000000

Regards,
Stefi



„ashg657” ezt írta:

Possibly with your strings in Column A

Sub RemoveLetters()
Dim NoLetterstring As String
Set myrange = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In myrange
Numberstring = c.Value
Start = 0
For L = Len(Numberstring) To 1 Step -1
   If Not IsNumeric(Mid(Numberstring, L, 1)) Then
   If found = False Then Start = Start + 1
   Else
       found = True
       NoLetterstring = Mid(Numberstring, L, 1) & NoLetterstring
   End If
Next L
a = Len(Numberstring)
b = Len(NoLetterstring)
c.Offset(0, 1).Value = Mid(Numberstring, (a - (b + Start)) - 1, b + 2)
NoLetterstring = ""
found = False
Start = 0
Next
End Sub

Mike

Here is a UDF that will do that.

To enter the UDF, <alt-F11> opens the VBEditor.  Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter the formula

=ExtractPattern(cell_ref) into some cell, where cell_ref is either a reference
to the cell containing the string, or the actual string.

=========================
Option Explicit
Function ExtractPattern(str As String)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[A-Z]{2}\d{7}"
Set mc = re.Execute(str)
If mc.Count >= 1 Then
   ExtractPattern = mc(0).Value
Else
   ExtractPattern = ""
End If
End Function
============================
--ron

Maybe this...

Assuming the N portion is *always* 7 characters and there are *no other
numeric characters* in the string:

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

Here is my UDF solution...

Function FindLLNNNNNNN(Cel As Range)
 Dim X As Long
 If Cel.Count = 1 Then
   For X = 1 To Len(Cel.Value) - 8
     If Mid(Cel.Value, X, 9) Like "[A-Za-z][A-Za-z]#######" Then
       FindLLNNNNNNN = Mid(Cel.Value, X, 9)
       Exit Function
     End If
   Next
 End If
End Function

Rick