First off, before answering your questions, here is a revised formula (the
previous one could not detect an 8 or more digit number in front of the 7
digit number you wanted to find...
=MID(A1,MIN(IF(ISNUMBER(--MID(SUBSTITUTE(A1," ","x"),ROW(1:99),7))*
ISERR(SEARCH("e",MID(A1,ROW(1:99),7)))*ISERR(FIND("/",MID(A1,ROW
(1:99),7)))*ISERROR(--MID(A1,ROW(1:99),8))*IF(ROW(1:99)>1,ISERROR
(--MID(A1,ROW(1:99),ROW(1:99)-1))),ROW(1:99))),7)
I also changed the limit to text strings up to 99 characters long. If you
could have longer text strings for this formula to process, then change
*all* the 99's in my formula to a number that is equal to the largest total
number of characters that your text could be. I also changed the formulas
cell reference from the arbitrary F5 I used in my previous submission to the
more standard A1 cell reference that is used when the actual cell reference
is unknown.
Okay, first off, the double minus sign is a means of converting the text
representation of a number into an actual numeric value. Excel will does
this conversion automatically whenever the text representation of a numeric
value is used in a numerical calculation. Putting the double minus sign in
front force Excel to attempt to multiply the text by minus one twice
(--TextNumber is the same as doing this...
-1*-1*TextNumber
If TextNumber is the actual representation of a numerical value, then that
number will be returned (because minus one times minus one is equivalent to
plus 1); if it is not the actual representation of a numerical value,
attempting to multiply it by the first minus sign (which is equivalent to
minus 1) will generate an error. So my code uses the double minus sign to
attempt to change the text at each point of the array's iteration to see if
ISNUMBER and ISERROR is true or not in order to calculate the values
necessary to retrieve the 7 digit number you are after.
And the above explanation is the key to understanding the second part of
your question. There are two problems with converting the text
representation of a number to an actual value... numbers that are powers of
10 (Excel uses and E, for exponent I'm guessing, to indicate a power of 10;
for example 1.23E4 is the same thing as 1234) and dates (which Excel works
with as offsets for January 1, 1900; so the human readable date of 7/20/2009
is really 40014). So, as the array formula iterates down the text, it will
would see text constructions like these 7 character long pieces of text...
1.23E45 and 07/20/09... as numbers when the double minus sign is applied to
them, so I needed to filter these "false positives" out if they occurred
before your actual 7 digit number. I handled this by looking for an embedded
"e" (either upper or lower case), for the E-Notation problem, or a date
separator symbol which is the slash in my system (but which can vary
depending on regional settings).