Extract an alphanumeric  string from a long text field in a MS Access query

darls15
darls15 used Ask the Experts™
on
Hello

Can someone please help me? I have a long text field which contains an alphanumeric reference which I'm trying to extract in my query and can't seem to get it right.

The string can appear anywhere within the field and begins with "Ref:MSG" and is followed by 8 numbers. These 8 numbers change for each record and there's usually characters or spaces either side of the string.

A couple of string examples are...
action the below request:^Note:  Ref:MSG24621668>^
CUSTOMER^COMMUNICATIONS RE: Ref:MSG61781623non-applicable

The result I am looking for would be...
Ref:MSG24621668
Ref:MSG61781623

If someone could help me with this it would be very much appreciated.

Thanks
darls15
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If length of expected result is always 15 symbols, you can use this construction in your query:
Mid([YourField],InStr(1,[YourField],"Ref:MSG"),15)

Author

Commented:
Awesome, thank you so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial