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
...
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.)

Maybe it's time for me finally to force myself to learn VB. I'm a former
programmer, but never learned VB.

Thanks for your suggestions.

"JoeU2004" wrote:

If the word "remedy" and a single space always precede the number, and if
the number is truly always 7 digits, you could use the following (A1
contains the original text):

=IF(ISNUMBER(FIND("remedy",A1)), MID(A1,FIND("remedy",A1)+7,7),
"missing")

Otherwise, it probably will be easier to write a user-defined function in
VB. However, the rules for finding the desired number must be precise.
For
example, is there always only one 7-digit number preceded by a space? Or
is
there always specific set of words ("remedy" and others) preceding the
number?

Basically, how would a human distinguish and find the number if he had to
scan the text left to right or right to left character by character or
word
by word, where a "word" is a sequence of non-space characters.


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

"Eric_NY" wrote in message
...
I have a column containing text values like "User requested authority of
emergency ID for reason NHUSER23 Restore of object to library
LEVEL2
under remedy 1074317. 06/04/09 17:46 QPGMR".

Some of the cells contain a 7-digit number and others don't. The
7-digit
number does not start at a fixed location in the text.

Is it possible to write a formula which looks for the 7-digit number,
and
returns the number if present, and another value (such as 0 or
"*MISSING*")
if not?

I'm using Excel 2003.

Thanks.