On Thu, 7 Feb 2008 03:21:06 -0800, ashg657
wrote:

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.

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