Help with LARGE Funtion

[vgmurthy]

Hello All,
This is my first post in this forum and really excited that I found this forum.

I tried searching for, before posting my below question, however couldnt get a clue to proceed further with my problem.

Here is my problem.

Background- I need to collect the consecutive 5 digit value from a given cell

I have a Cell A1 that has a content as below


#46151
java.awt.LightweightDispatcher.retargetMouseEvent
java.awt.LightweightDispatcher.processMouseEvent
java.awt.LightweightDispatcher.dispatchEvent(Container.java:XXX1

Im using the formulae as =LARGE(VALUE(--MID(E120,ROW(INDIRECT("1:"&LEN(E120))),5)*1),1)

Upon an F9 on VALUE(--MID(E120,ROW(INDIRECT("1:"&LEN(E120))),5)*1) I get as below

{#VALUE!;46151;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1}

But when I use LARGe function to pick the first largest from this Array, it return as below (F9 on the above formula)

#VALUE!

Same is the result of the formula.

Can you please correct me where I need to correct. My intention is get the Highest value. I tried with <mid(e121,row(indirect("1:"&len(e121))),5)+0)), but="" it="" returns="" a="" value="" of="" 1,="" as="" the="" first="" hit="" for="" no="" match="" 10^5="" is="" 1.=""
=LOOKUP(10^5,MID(E121,ROW(INDIRECT("1:"&LEN(E121))),5)+0), but this returns the value as 1. (Think it couldnt find a match for 10^5 and hence it returned the first hit of 1)

Please help.

Thanks in advance,
Venu</mid(e121,row(indirect("1:"&len(e121))),5)+0)),>

 

[Domenic]

For your LARGE formula, you'll need to a condition that checks whether each value within the returned array is a numerical value. However, there may be a more efficient way to extract your 5 digit number. Does the number always start from the second character position? If not, is it always preceded by the pound (#) symbol?

 

[vgmurthy]

Hi Domenic,
Thanks for your reply. The position is random in nature and can occur anywhere within the cell. Also it doesn't preceded with any symbol.

Regards,
Venu

 

[Domenic]

If the 5 digit number is always the first set of numbers that occur in the text string, you can simply use the following formula...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),5)

 

[vgmurthy]

Hi Domenic,
My number string (length 5 occurs anywhere within my cell. Please note that my cell may also a set of 2 number or 3 numbers (length). The formula that you have given is pulling the records when the first hit for a number is encountered.

Regards,
Venu

 

[Domenic]

If your text string does not contain a number that is greater than 5 digits in length, try...

=LOOKUP(9.99999999999999E+307,MID(SUBSTITUTE(A2," ","x"),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A2)-5+1)),5)+0)

Note that if there are 2 or more numbers that are 5 digits in length, the formula will return the last occurrence.

Does this help?

 

[XOR LX]

=LOOKUP(9.99999999999999E+307,MID(SUBSTITUTE(A2," ","x"),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A2)-5+1)),5)+0)

I'm not a massive fan of this LOOKUP set-up. Although it will work in the vast majority of cases, it is not guaranteed to do so.

For example, if A2 contains:

#46151java.awt.LightweightDispatcher.retargetJun23MouseEventjava.awt.LightweightDispatcher.processMouseEventjava.awt.LightweightDispatcher.dispatchEvent(Container.java:XXX1

then, given of course the appropriate date/language settings, this formula will return 45078, not 46151.

Or, if A2 contains:

#46151java.awt.LightweightDispatcher.retarget.1E07MouseEventjava.awt.LightweightDispatcher.processMouseEventjava.awt.LightweightDispatcher.dispatchEvent(Container.java:XXX1

then the result will be 1000000.

Very unlikely events, you might argue, though perhaps not impossible.

More rigorous, though longer, is, for example:

=MID(A2,MATCH(7,MMULT(ABS(ISNUMBER(0+MID(MID("ζ"&A2&"ζ",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)-4)),7),{1,2,3,4,5,6,7},1))-{1,0,0,0,0,0,1}),{1;1;1;1;1;1;1}),0),5)

Regards

 

[Domenic]

@ XOR LX

While, as you say, those events are unlikely, any risk no matter how small should be eliminated. So thanks for pointing it out!

Cheers!

 

[XOR LX]

@Domenic

Ah, wait. I'd forgotten about an idea of Lori's which might just rescue the LOOKUP set-up with regards to these date/scientific strings:

=-LOOKUP(1,-(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)-4)),5)&"**0"))

(I took the liberty of negating the lookup_vector as well so as to avoid using "BigNum").

Regards

 

[Domenic]

@ XOR LX

I didn't know that two consecutive asterisks could be used to convert a value to scientific notation. Interesting!

Also, while it's unlikely that we'll have spaces within the text string, such as...

#46151
java.awt.LightweightDispatcher.retargetMouseEvent
java.awt.LightweightDispatcher.processMouseEvent
java.awt.LightweightDispatcher.dispatchEvent(Container.java:XXX 1

...we can eliminate the risk by replacing them with an alpha-character. Actually, I think we can simply trim the text string...

=-LOOKUP(1,-(MID(TRIM(A2),ROW(INDEX(A:A,1):INDEX(A:A,LEN(TRIM(A2))-4)),5)&"**0"))

Or, with BigNum defined as 9.99999999999999E+307...

=LOOKUP(BigNum,(MID(TRIM(A2),ROW(INDEX(A:A,1):INDEX(A:A,LEN(TRIM(A2))-4)),5)&"**0")+0)