On Sat, 18 Jul 2009 04:40:01 -0700, Lori
wrote:

I'm not sure what you mean by "numbers that are part of other strings".
I thought that's what was wanted but i'm probably missing something.

Sorry. That statement was not clear.

What I meant is that your routine seems to return seven digits from substrings
that are longer.

NH1234567890 -- 4567890
45678901234 -- 8901234

I interpreted the OP's requirements to indicate that he wanted to extract the
first seven digit WORD; whereas your routines extract the first (or last) seven
consecutive digits, even if they are part of another word.

In the regex I presented, WORD is defined as a seven digit string of digits
surrounded by a non-word character. A non-word character is anything except a
digit, letter or underscore.

So, mine is somewhat flawed in that it would return seven digits if they were
prepended by, let us say, an asterisk or ampersand; but it would return
**MISSING** in the above instances.

But given &1234567*, mine would also return the seven digits. This could be
taken care of, if necessary, by defining word a bit differently.
--ron

Good points although I'm pretty sure this is academic from the parenthesised
reply to JoeU above. From the context given, if it's known there's no more
than one seven digit number it seems unlikely there are any longer numbers
either.

Given the high risk of typos in freeform text fields it's possible a udf may
be more reliable but there are just too many unknowns. A thorough check will
need to take place whatever the method.


"Ron Rosenfeld" wrote:

On Sat, 18 Jul 2009 04:40:01 -0700, Lori
wrote:

I'm not sure what you mean by "numbers that are part of other strings".
I thought that's what was wanted but i'm probably missing something.

Sorry. That statement was not clear.

What I meant is that your routine seems to return seven digits from substrings
that are longer.

NH1234567890 -- 4567890
45678901234 -- 8901234

I interpreted the OP's requirements to indicate that he wanted to extract the
first seven digit WORD; whereas your routines extract the first (or last) seven
consecutive digits, even if they are part of another word.

In the regex I presented, WORD is defined as a seven digit string of digits
surrounded by a non-word character. A non-word character is anything except a
digit, letter or underscore.

So, mine is somewhat flawed in that it would return seven digits if they were
prepended by, let us say, an asterisk or ampersand; but it would return
**MISSING** in the above instances.

But given &1234567*, mine would also return the seven digits. This could be
taken care of, if necessary, by defining word a bit differently.
--ron