Help with LARGE Funtion

vgmurthy

New Member
Joined
Mar 6, 2015
Messages
3
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)),>
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,106
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

New Member
Joined
Mar 6, 2015
Messages
3
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,106
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

New Member
Joined
Mar 6, 2015
Messages
3
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,106
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
=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
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,106
@ 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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
@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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,106
@ 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)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,240
Messages
5,413,256
Members
403,468
Latest member
GRamos

This Week's Hot Topics

Top