twist on nth ocurrence of text in column

[DThib]

I have the formula below working but I need it to pull a portion of the text found not the whole string. My fix is from another formula I worked out. but it is throwing an error.

Can anyone help me fix this to work? I have bolded what I need it to find for every iteration. It is supposed to find the word and then pulls the number after.

=INDEX('Run Data'!$A:$A, SMALL(IF(ISNUMBER(SEARCH("Run", 'Run Data'!$A:$A)),ROW(MID('Run Data'!$A:$A,SEARCH("Run", 'Run Data'!$A:$A)+3,2)),2)))

Finds Example entire string: 1120-01X TK Run15 19Nov2008 Plate01: Expired Date Table

DThib

 

[Nine Zero]

So in the example given your result would be 15?

is it always a 2 digit number?

is there always a space after the number?

 

[DThib]

Yes, this is the format.

DThib

 

[Nine Zero]

Assuming your data is in column A starting with row 1

=MID($A1,FIND("Run",$A1)+3,2)

then just copy down

 

[DThib]

Thanks. My quandary is that the file will be copied down. So, the basic title will repeat and the information will follow.
I need to draw the second occurrence in one column and the nth occurrence in the next.
This will allow only the first iteration. I have used it and it does not look beyond the first instance.

Assuming your data is in column A starting with row 1

=MID($A1,FIND("Run",$A1)+3,2)

then just copy down

 

[DThib]

Any suggestions?

Thanks. My quandary is that the file will be copied down. So, the basic title will repeat and the information will follow.
I need to draw the second occurrence in one column and the nth occurrence in the next.
This will allow only the first iteration. I have used it and it does not look beyond the first instance.

 

[Rick Rothstein]

See if this works for you...

=LEFT(TRIM(MID(SUBSTITUTE(A$1,"Run",REPT(" ",300)),ROWS(A$2:A2)*300,300)),2)

 

[DThib]

Thanks Rick.

It does not pull anything but the first stated title.
I think I may have not explained well.

The data will come from different runs. Each file will have a title similar to the example I gave. This data runs approximately 90 rows and 20 columns each iteration. The owner of the data wants to have all runs appear in the same tab. running down. So the next run will have data appearing on the next empty row with approximately the same row and column count. All titles will appear in Column A. They will basically have a run number as the variable. That is what I need to call.

=INDEX('Run Data'!$A:$A, SMALL(IF(ISNUMBER(SEARCH("Run",'Run Data'!$A:$A)),ROW('Run Data'!$A:$A)),3))

</span>
This will pull the entire line but I need only the number

DThib

 

[DThib]

Anyone have a suggestion for how to pull the second occurance from the formula"

=INDEX('Run Data'!$A:$A, SMALL(IF(ISNUMBER(SEARCH("Run",'Run Data'!$A:$A)),ROW('Run Data'!$A:$A)),3))

It pulls the entire second or third title as needed but I only need to pull the changing number after "run".
I cannot figure this one out.

DThib

 

[DThib]

I am trying this formula to pull the numbers I need, but it keeps giving me a #NUM result.

=INDEX('Run Data'!$A:$A,SMALL(IF(ISNUMBER(MID([COLOR=#222222][FONT=Verdana]'Run Data'!$A:$A,[/FONT][/COLOR]SEARCH("Run",'Run Data'!$A:$A)+3,2),ROW('Run Data'!$A:$A)),2))

If I leave out the MID function I pull the whole 2nd occurrence but not just the 2 numbers after "RUN".

=INDEX('Run Data'!$A:$A,SMALL(IF(ISNUMBER(SEARCH("Run",'Run Data'!$A:$A)),ROW('Run Data'!$A:$A)),2))

Will pull the entire 2nd occurrence text: 190-014S TK Run15 19Mar2018 Plate01: Experiment Data Table

Can anyone help?