PDA

View Full Version : Extracting A Substring



nbqleebarnes
03-15-2010, 04:11 AM
Hi tpoynton and lucas,
I have a very similar problem although I tried using
=--MID(A1,FIND(":",A1)+7,LEN(A1)-FIND(":",A1)-3)
but I can't get it right for the following data field examples:-
'Item : D0101 - Alfa 67-77 Opel Kadett 66-73(F)
and
'Item : 098650A094 - D3133
and
'Item : D0277/1 - Renault R5 R9 R11 77-86
where I need to extract the Dxxxx or if applicable Dxxxx/1
Would be greatful for the help
Thanks
Lee

GTO
03-15-2010, 04:35 AM
...deleted...

Bob Phillips
03-15-2010, 04:37 AM
You should start a new thread, not tag onto someone else's. I have moved this to its own thread.

You could try

=MID(A1,FIND(":",A1)+2,FIND(" ",A1,FIND(":",A1)+2)-FIND(":",A1)-2)

GTO
03-15-2010, 04:40 AM
Sorry Bob, you are just too fast!!!

Hi Lee,

Not well tested, but if we can count on 4 digits following a "D", and optionally/possibly a slash followed by 1 or more digits, maybe as a UDF:


Option Explicit

Function RetD(CellAddress As String) As String
Static REX As Object ' RegExp
Dim rexMatch As Object ' MatchCollection

If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = False
.IgnoreCase = False
.Pattern = "\bD[0-9]{4}(/[0-9]+)?\b"
End With
End If

With REX
If .Test(CellAddress) Then
Set rexMatch = .Execute(CellAddress)
RetD = rexMatch(0)
Else
RetD = "Not Found"
End If
End With
End Function


Hope that helps,

Mark

nbqleebarnes
03-15-2010, 04:47 AM
Thanks XLD and GTO,
Will try the coding, and make sure I have my own thread next time.
Cheers
Lee