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

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

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: