Extract data basis pattern 3 examples attached

[Asheesh]

Guys, Again need your expertise to help me through
In the attached there are 3 problems..I need to extract 2 numbers from each problem..these numbers follow a pattern
i.e. "??-???????" and is of length 10.
Please see the attached...
Note: this is a sample and ranges will vary in each problem..
I am looking primarily looking for formula solution because I have a data model already built using formulae.
Appreciate your kind help in advance..

EDIT:

Also one more point: we see these patterns in row 13 & 35 for all the three problems..however, that is not the real case
these patterns can be anywhere in the range

 

[tomas]

I don't know how you want the output look like. In first columns ?? and second column ????????. or somehow else?

 

[Asheesh]

@tomas

Result should be as is see Columns E, F & G..

i.e. ??-??????? two digits followed by dash and then seven digits...

 

[Asheesh]

With respect to EDIT in post 1 - see the amended attachment

 

[tomas]

Then it would be just =left(cell reference,10)

 

[Asheesh]

@tomas
Nope that is not it...

First, I need these values one beneath the other on a different tab...

Second, I do not know their specific location..

 

[Asheesh]

I have come up with below...

INDEX(LEFT($A$1:INDEX($A:$A,COUNTA($A:$A)),10),MATCH(SMALL(IF(ISERR(SEARCH("??-???????",LEFT($A$1:INDEX($A:$A,COUNTA($A:$A)),10))),"",ROW(INDIRECT("1:"&COUNTA($A:$A)))),ROW(A1)),ROW(INDIRECT("1:"&COUNTA($A:$A))),0))

See attached for your refernce..

Perhaps, someone has a better or simpler way out

 

[tomas]

hm

I also haven't find something satysfying . Just two formulas for first and second occurance of number in column. But it works only if - is on third position which I can see is not completely reliable pattern

 

[Deepak]

Hi Asheesh,

Is this one...

E3 =LEFT(INDEX(A:A,SMALL(IF(MID(LEFT(A2:A39,10),3,1)="-",ROW(A2:A39),""),ROW()-2)),10)