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


"ashg657" wrote in message
...
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.