Need formula to extract a numeric value from a free-format text

Thanks. I'll need to take some time to decipher this. But first, can you
explain:

- What's the function of the double minus sign in the formula?
- Why does the date separator come into play here at all? The only thing I
need to do is to look for a 7-digit number in a text field where we know
nothing at all about the rest of the contents. Why should the operation of a
formula be dependent upon extraneous information, such as how a date is
formatted? The rest of the text string needs to be considered as a fully
arbitrary series of characters; in other words, the function needs to work
entirely irrespective of the remainder of the field.

Thanks for your help.

"Rick Rothstein" wrote:

I forgot to include the note regarding array-entered formulas. Here is my
message again, but with the note...

In case you want to consider it, here is a non-RegEx array-entered** formula
that will do what you want...

=MID(F5,MIN(IF(ISNUMBER(--MID(SUBSTITUTE(F5," ","x"),ROW(1:30),7))*
ISERR(SEARCH("e",MID(F5,ROW(1:30),7)))*ISERR(FIND( "/",MID(F5,ROW
(1:30),7))),ROW(1:30))),7)

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Note though, that this formula is dependent on what your default date
separator is. Mine is the slash character (/) and that is what I used in the
FIND function call... if your default date separator is a different symbol,
then just replace my slash with that character.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
In case you want to consider it, here is a non-RegEx array-entered**
formula that will do what you want...

=MID(F5,MIN(IF(ISNUMBER(--MID(SUBSTITUTE(F5," ","x"),ROW(1:30),7))*
ISERR(SEARCH("e",MID(F5,ROW(1:30),7)))*ISERR(FIND( "/",MID(F5,ROW
(1:30),7))),ROW(1:30))),7)

Note though, that this formula is dependent on what your default date
separator is. Mine is the slash character (/) and that is what I used in
the FIND function call... if your default date separator is a different
symbol, then just replace my slash with that character.

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
I just read your message from last Friday.

The text is free format. Users can enter it in whatever format they want.
The 7-digit number is somewhere within the text. I've glanced through it
and
in the samples I've seen, there's no consistency in what appears before
or
after the 7-digit number.

I used the regex solution that Ron Rosenfeld suggested, and adjusted the
regular expression by removing the "\b" before and after the "\d{7}".

"Rick Rothstein" wrote:

I would still be interested in the answer to my questions...

"Is the number always precede by a space when the number
is interior to the text? What about that "dot" after it... is there
always a dot following it? If the dot might not always be
there, is there always a space after the number when it is
interior to the text?"

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
"However, with the morefunc add-in there is an option, I believe it is
a
menu
option, to install the add-in as part of the workbook. "

I can't find that option. Where should I look for it? Which menu?

"Ron Rosenfeld" wrote:

On Thu, 16 Jul 2009 12:04:04 -0700, Eric_NY

wrote:

Ron - Thanks. I'm going to try the Longre morefunc add-in.

If I use the add-in functions and then send the sheet to someone by
email,
will the formulas still work? Or does the recipient also have to
install
the
add-in separately on his own machine?

Thanks for your help.

Ordinarily no. They would have to install it themselves. However,
with
the
morefunc add-in there is an option, I believe it is a menu option, to
install
the add-in as part of the workbook. If you do that, it will then be
usable by
the recipient with no particular effort on his part.

One caution concerning the add-in -- it will not work on strings that
are
longer than 255 characters. This, apparently is an .xll limitation,
and
there
is no good way around it within the add-in.

By the way, if you should use the UDF approach, the UDF should be
embedded
within the workbook, so its use should be transparent to your user.
But
there
are a lot of other useful functions in morefunc.
--ron