Re: Need formula to extract a numeric value from a free-format tex

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I needed to make one more modification to my formula to correct an area where it incorrectly missed the 7 digit number. I have tested this version and I now believe it to work correctly in all situation. It is still an array-entered** formula...

=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&"x",ROW(1:99),8))*ISERROR(--MID(
"x"&A1,ROW(2:100)-1,1)),ROW(1:99))),7)

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

--
Rick (MVP - Excel)


"Eric_NY" <EricNY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:56DE137B-79C2-412B-814A-4E64DE9B19EE@xxxxxxxxxxxxxxxx
Very clever!

I've seen that double minus before and never understood the point. Thanks
for explaining.

Where in your formula is the logic which checks for the number starting
first in character position 1, then position 2, then 3, etc.? The only way I
could think of was something unwieldy, such as:
IF(ISNUMBER(MID(A1,1,7)),MID(A1,1,7),IF(ISNUMBER(MID(A1,2,7)),MID(A1,2,7)....
)))))))). Obviously that's not a great solution.

Yours is obviously more compact and better - if I can figure out how it works!

Thanks again for your help.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Eric_NY" <EricNY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71DB9352-C9BB-4D3C-B7E1-A4472C48F43E@xxxxxxxxxxxxxxxx
> 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" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in >> message
>> news:%23lJyL5UCKHA.4376@xxxxxxxxxxxxxxxxxxxxxxx
>> > 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" <EricNY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> > news:ADF5A7F2-528A-49B6-83BE-5B625E33D063@xxxxxxxxxxxxxxxx
>> >>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" <EricNY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >>> news:6D1CC44F-C9E9-41D4-ABF1-BBF27BD6C2D1@xxxxxxxxxxxxxxxx
>> >>> > "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
>> >>> >> <EricNY@xxxxxxxxxxxxxxxxxxxxxxxxx>
>> >>> >> 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
>> >>> >>
>> >>>
>> >>>
>> >
>>
>>



.

Relevant Pages

  • Re: Need formula to extract a numeric value from a free-format tex
    ... 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... ... 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... ... So, as the array formula iterates down the text, it will would see text constructions like these 7 character long pieces of text... ...
    (microsoft.public.excel.misc)
  • Re: lookups referring to formulas
    ... still a string to excel. ... RagDyer's formula checks to see if that left most character is a digit. ... >> I am trying to use the vlookup function to add detail relating to one figure ...
    (microsoft.public.excel.misc)
  • Re: Recommended typefaces
    ... have mentioned as a decently visible minus sign? ... The en dash is a different character, ... I think probably means the minus key on the numeric pad, ... Unicode 2212 is a true minus sign but Excel doesn't understand true ...
    (alt.usage.english)
  • Re: Automated-backup script
    ... > 1) download the backup file ... > character of file, if it is a digit, minus 1) ...
    (alt.linux)
  • Re: Need formula to extract a numeric value from a free-format tex
    ... I've seen that double minus before and never understood the point. ... would see text constructions like these 7 character long pieces of text... ... Rick ... If I use the add-in functions and then send the sheet to someone ...
    (microsoft.public.excel.misc)