Extracting text from a field

[AaronWestcott via AccessMonster.com]

I hope someone can help. I have read through the posts on regular
expressions and am still at a loss.

I have a field in a table that contains text data that looks like:

HPWLY CXLUMA 81 295 10930149 RJ 66 CHTM

I am trying to extract "81 295 1093" and ultimately get "811093" (thei
first and last group concatenated.

The challenge is that all groups are variable length. The pattern is that I
want the first instance of 2 digits from the left and the first 4 digits
after the second group of numbers.

I want to take this information and put it in a new field.

I am using Access 2003.

I hope someone can poitn me in the right direction.

Thanks.

 

[Jeff Boyce]

Aaron

Are you saying that the field contains multiple facts that you're trying to
parse out? Any chance you can get the individual fields rather than one big
one?

Take a look at the Split() function ... it generates an array that you'd
need to grab pieces out of. I'm not sure if it handles multiple spaces ...
you might need to use the Replace() function repeatedly to replace " " with
" " (two spaces with one).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AaronWestcott via AccessMonster.com]

Jeff,

I ran the replace function multiple times until I was assured that only one
space separated the data groups and then used the split function. this
worked very well and I was able with little further effort to account for
different patterns. I got the data I wanted.

Thank you.

Aaron

Are you saying that the field contains multiple facts that you're trying to
parse out? Any chance you can get the individual fields rather than one big
one?

Take a look at the Split() function ... it generates an array that you'd
need to grab pieces out of. I'm not sure if it handles multiple spaces ...
you might need to use the Replace() function repeatedly to replace " " with
" " (two spaces with one).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I hope someone can help. I have read through the posts on regular
expressions and am still at a loss.

[quoted text clipped - 18 lines]

Thanks.