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.

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.

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

"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.

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

Maybe this...

LLNNNNNNN
(L= Letter, N = Number)

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&"0123456 789"))-2,9)

--
Biff
Microsoft Excel MVP


"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.

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.