Finding a word in a range and returning the number that follows by formula

[DThib]

Good morning,

I have another task. My task got more complicated. The owner wants to place all files in one worksheet right under the previous. So with this, the title of the txt file that will be placed in will have the File number along with other information. I need to find the file number for the correct file and then again, in another column get the 2nd file and so on. The result will be in a separate worksheet and each file will be in a different column.

I have tried search, Mid and get nowhere. A3 is cell with the identity of the sample in the file

=IF($A3="","",IFERROR(MID('File Data'!$A:$A,FIND("File",'File Data'!$A:$A)+1,2),"Blahh"))

Help?!

DThib

 

[DThib]

Hey,

I managed to get the entire string for the first instance but I really need just the file number. How do I make sure the file number after the word File is taken?

=IFERROR(INDEX('File Data'!$A:$A,MID(IF(FIND("File",'File Data'!$A:$A),ROW('File Data'!$A:$A),"False"),1,2)),"Nuts!"

result = entire string found by Run. Not sure if it finds this because it is the first line. I will also need to find the next file in another column.

 

[DThib]

This will get 1 number but not the entire number

=LEFT(MID('File Data'!$A:$A,SEARCH("File",'File Data'!$A:$A)+3,255))

Help, please!!!!!