Parse - detect letter number combination 2

[Dave177]

Is there away of detecting and isolating a unique letter/number combination?
For example if you think that somewhere (at random) in a field there may be a letter/number combination
eg:
AA56B89
BT44D99
CG56D27

How would you isolate these 7 characters or return null if no such combination existed? The field sometimes has text of about 80 characters and the 7 that I am interested in are at random parts of it. However the combination is always - letter,letter,number,number,letter,number,number

Is it possible?

 

[fneily]

Have a look at the Instr() function. GoTo VBA (F11), type Instr(, highlight it then hit F1. If you look at the examples, you'll see it returns 0 if string isn't found.

 

[Dave177]

Thanks for your reply.
I don't think I explained the problem very well. I basically need to test whether each character is a letter or number and then find and isolate the string of 7 characters if they match the correct letter/number combination. So I don't know what the string itself is I just know that there it is a letter,letter,number,number,letter,number,number combination.

Dave

 

[PWise]

do you a list of all combination if yes

run this query

Select longfieldname ,instr(longfieldname ,listtablename.listfield)
from table ,listtablename

0 will mean not found

 

[traingamer]

Is there away of detecting and isolating a unique letter/number combination?

You can detect it using something like:

select * from yourtable
where yourfield like "*[a-z][a-z]##[a-z]##*"

but that won't give you the location in the string. You could write a function utilizing Split and walk through the results.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[willybgw]

The following function which uses Greg's excellent pattern finder will do what you want.

Function FindAndCapture(INFIELD As String) As String

Dim X As Integer
Dim CNT As Integer
Dim TARGET As String

CNT = Len(INFIELD) - 6
X = 0
START:
If X < CNT Then
X = X + 1
Else: GoTo ENDIT
End If

TARGET = Mid(INFIELD, X, 7)

If TARGET Like "*[A-Z][A-Z]##[A-Z]##*" Then
FindAndCapture = TARGET
GoTo ENDIT
Else: GoTo START
End If


ENDIT:

End Function

willybgw

 

[Dave177]

Thank you both very much!