Find string that matches a pattern (Instr?) 2

[wes252]

I have a string that can be up to 240 characters long and can contain many dashes and in any position. I want to find and extract the string that matches the pattern of “?-####-?” or “?-###-?”

For example, in the data below I want to extract C-8539-C from the the 1st line and F-818-A in the 2nd line, etc.

C-8539-C 7.50 TO 9.00 J784-780
TO COVER RELEASE OF 5000, F-818-A CAGE
CARBURIZE AND HARDEN C-8471-A JOB# 270420
47294-OR-CORR#3085 HARDEN C-8571-A JOB# 270424
CARBURIZE C-8663-C JOB# 267644
HEAT TREAT F-5621-A JOB# 275025
SURCHARGE FOR C-8658-C

Can someone help me?

Thanks,
Wes

 

[Blorf]

Extract each occurance of ?-####-? to a single record?

Can do this with vba code to examine the string and find occurances and write to a table.

Each string can be up to 240 chars, but can also be quite shorter?

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[CautionMP]

wes252,
Here is a concept to get you moving in the right direction. This was done in Excel ([tt]Worksheets("Sheet1").Range("B2")[/tt] hold your sample data) but will work the same in Access.

Sub ArrayFilter()
Dim i As Integer
Dim s() As String
s = Split(Worksheets("Sheet1").Range("B2"), " ")
For i = 0 To UBound(s)
  If s(i) Like "?-####-?" Or s(i) Like "?-###-?" Then
    Debug.Print "Substring " & i, s(i)
  End If
Next i
End Sub

For me the output to the Immediate window looked like this:

[tt]Substring 0 C-8539-C
Substring 9 F-818-A
Substring 13 C-8471-A
Substring 18 C-8571-A
Substring 22 C-8663-C
Substring 26 F-5621-A
Substring 30 C-8658-C[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Blorf]

Neat.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[wes252]

Thanks! Exactly what I needed.