Thanks for the clarification so the formula i posted above should be robust.
Did you any luck with it?



"Eric_NY" wrote:

The text is user-entered and free-format. It can contain anything. Except in
case of input error, it always contains the 7-digit number, which is what I'm
trying to extract.

"Rick Rothstein" wrote:

I saw that, but was wondering if that 7-digit number could ever be encased
in text like this... abc1234567def... hence my question about a leading
space (assuming the number were not at the beginning of the text) and a
following "dot" (as shown in his example) or possibly a following space to
offset it from other text. I've been playing around with an array-entered**
formula concept like this...

=MID(F5,MIN(IF(ISNUMBER(--MID(SUBSTITUTE(F5," ","x"),
ROW(1:30),7)),ROW(1:30))),7)

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

I'm not really concerned about the leading space any more (the SUBSTITUTE
function has taken care of that), but I am interested in what trails the
7-digit number. Of course there are still problems with the above formula;
for example, things constructed like this...17Jul09... and like this...
1.23e45... will register as "7-digit numbers". Unfortunate, the first 7
digits of a number longer than 7 digits will also register as a "hit". I
just figured it would be nice to know if there were *any* formatting type
things to lock onto before attempting to patch the above formula any
further. As a matter-of-fact, if the OP is reading this... I would also like
to know if the text could contain 8-digit or longer numbers, date-looking
text like 17Jul09 and floating point power of ten numbers like 1.23e45?

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
I would still be interested in the answer to my questions...

In response to my questions, Eric wrote:

"Unfortunately there's no precise format. I just need to look for a
7-digit
number, which can be anywhere in the string. (I could restrict it to
looking
for the first 7-digit number, since I know that there's never more than
one.)"


----- original message -----

"Rick Rothstein" wrote in message
...
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