Apologies, the C's should have been A's in the formula above. To allow
for blanks and ignore -s in formulas try instead filling down from B1:

=LOOKUP(9^9,SEARCH(SUBSTITUTE(Sheet2!A$1:A$250,"-",""),
SUBSTITUTE(A1,"-",""))/(Sheet2!A$1:A$250<""),Sheet2!A$1:A$250)

and to look for a second ID, fill down in column C:

=LOOKUP(9^9,SEARCH(SUBSTITUTE(Sheet2!A$1:A$250,"", ""),
SUBSTITUTE(SUBSTITUTE(A1,B1,""),"-",""))/(A$1:A$250<""),Sheet2!A$1:A
$250)

Results:

B C
713005L 7115-0003
71420164 #N/A
7115-0011 #N/A

On 29 Mar, 11:01, wrote:
Thanks, this just returns a '0' where there is text or 'N/A' where it
si blank.

I want it to actually extract the ID from the random text. Can i
modify it to do this?