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:

Complex one here I think.....

I have a coumn of strings, for example:

CPSAINSBURYSAE0401001
CPAE0401001SAINSBURYS

..what I need to be able to do is extract from the strings the following:

LLNNNNNNN
(L= Letter, N = Number)
So if we applied this to the above examples, we would have a result of:

AE0401001

Little bit confusing, hope its clear enough, really need help on this one.
Many thanks.
Ash.