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

 

[XOR LX]

Domenic.

Yes - I was quite surprised when I first saw that syntax for scientific notation! Nice, eh?

Re spacing, I can't seem to replicate this issue. Pasting the string you give results in 4 cells' worth of entries in my Excel, and manually inserting spaces around the desired extraction does not seem to affect the result of the version which doesn't use TRIM.

Can you clarify, perhaps with a shorter example in which the spacing is obvious?

Many thanks.

 

[Domenic]

@ XOR LX

It looks like the Board "trimmed" the text string when I posted it. I should have posted it within HTML code tags. So if the text string contains 4 or more spaces before the number 1 at the end of the string like so...

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

...the formula would return 1.

 

[XOR LX]

@Domenic

Yes, of course. Many thanks.

And certainly your TRIM version is necessary then.

Cheers.