Re: find a number in a cell

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

I'm not 100% sure why this works, but it does seem to work. This formula removes one of the SUBSTITUTE function calls and arrays the "find me" text (saving some 15 characters in the process)... and it seems to always return the correct answer too:

=LOOKUP(1000,--MID(SUBSTITUTE(LOWER(A1),{"a","p"},"z"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9},SEARCH(" at ",A1))),ROW(INDIRECT("1:255"))))

Rick


"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message news:e4stQOSFIHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
Yeah, that'll work. I didn't have time to dig any deeper and look for a tweak.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in message news:Oy9zNBRFIHA.1056@xxxxxxxxxxxxxxxxxxxxxxx
So, as long as the next character after the last number that
is <12 <> "a" it will work!

Well, not exactly... if the character is a "p", you will have the same problem (for the same reason). Here is a patch to your formula to fix this problem...

=LOOKUP(1000,--MID(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"p","z"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9},SEARCH(" at ",A1))),ROW(INDIRECT("1:255"))))

Yes, your formula is now longer, but it still is around half the size of the one I posted.

Rick



.

Relevant Pages

  • Re: [PATCH 0/5] I8K driver facelift
    ... can we be a little more explicit and not poke in the sysfs guts right ... What do you think about the patch below athat implements ... | arrays so they can be tested. ... send the line "unsubscribe linux-kernel" in ...
    (Linux-Kernel)
  • Panic in todays current, UFS problem?
    ... I was trying out the new patch from Ville-Pertti Keinonen that might fix my ... interrupt storm problems with SATA when I ran into a panic. ... fdisk/label two 250GB discs hooked up to the on-board SATA controller ... geom_vinum used (raid-0 and raid-1 arrays) ...
    (freebsd-current)
  • [PATCH 6/10] x86 boot: proper use of ARRAY_SIZE instead of repeated E820MAX constant
    ... This patch is motivated by a subsequent patch which will allow for more ... memory map entries on EFI supported systems than can be passed via the x86 ... size for several arrays and loops over those arrays. ... * replaces the original e820 map with a new one, ...
    (Linux-Kernel)
  • Re: [PATCH/RFC] hwmon: Add support for W83667HG-B
    ... Is it just me or these arrays aren't used anywhere? ... Anyone willing to add support for this ... preliminary patch, and then your patch adding support for the ... write to a non-existing register. ...
    (Linux-Kernel)
  • [PATCH 00/10] x86: Reduce Memory Usage and Inter-Node message traffic (v3)
    ... data arrays and is referenced against 2.6.23-rc6. ... the values in these arrays are referenced by the cpu ... Of course, if the referencing cpu is not on node 0, then the ... This patch is referenced against 2.6.23-rc6. ...
    (Linux-Kernel)