Searching for a String with Pattern

[nrage21]

I posted this thread in the Excel area, then I realized that I can do this much easier in Access.

I need help creating a VB function.

I have a column with text (not exceeding 255 chars). Within the text, there is a specific 8 character string I'm interested in pulling (2 letters, 5 numbers, 1 letter) <= this is always the pattern (i.e. AB12345C).

The function needs to search through the text and when it finds the first instance return the string. If the function does not find any string, then it should return nothing.

Any help is greatly appreciated

This is a sample of the data I have in the column of my table

Notes
12342 John Doe AB12345C EN ME
897342 Jane Doe DE54321F EN ME
8923 Peter Doe GH23456I EN ME
40293074 Jamie Doe JK34567L EN ME
2093 Frank Doe MN45678O EN ME 298730 Alex Doe PQ98765R EN ME

This is the VB function I want to modify to include the specific pattern [A-Z]{2}[0-9]{5}[A-Z]{1}

I know I have to change the following argument to accomodate my string, but I don't know how:

IsNumeric(varString(intCounter))

Please Login or Register  to view this content.

[alansidman]

If you are looking to do this in Access, then why not just create a query and in the criteria put Like *AB12345C* and then Access will bring back all records having this string in the field you identify. You don't need any VBA to do that. If you need further machinations on the records identified, you can export back to Excel and slice and dice to your hearts content.

Alan

[Marcol]

This is easily done in Excel with a UDF, if you decide to use it instead of Access

Please Login or Register  to view this content.

Enter in Excel

Please Login or Register  to view this content.

to use " " as your delimiter (default)

Please Login or Register  to view this content.

as an example ";" (semi-colon)

Drag/Fill down as required

Hope this helps

[nrage21]

Thx for the reply alansidman. However, the list I have to work with have thousands of records with different variations, but the pattern is always the same 2 alpha; 5 numbers; 1 alpha.

Marcol, you just gave me some ideas... and thx also for your reply.

I'll post back if I'm able to make this VB code work.

[DonkeyOte]

FWIW, see also the Excel thread and UDF: http://www.excelforum.com/2419378-post5.html

[nrage21]

Thx DonkeyOte for your help and insight. You can mark this post 'Solved'

I ended up modifying Marcol's code in Access and it worked like a charm. For those of you interested, this is the end result:

Please Login or Register  to view this content.

[DonkeyOte]

I'm glad you have resolved but obviously you're not being rigorous regards your checks using this approach - ie @@12345! would pass as valid.

If you wanted to avoid RegExp and still use a robust test then you can use Like [edit: have not tested re: Access mind you!], eg:

Please Login or Register  to view this content.

not use of Exit For means you will only ever return first match should multiple exist - I presume this remains the intention.