Using the LOOKUP function to extract numbers from sentences

Hi,

I refer to a previous equation I've seen on the forum and I want to extract numbers from a sentence and have it onto another row.

eg. A1: 1 ALL OTHERS

to have 1 on say K1

I used the following equation:

=LOOKUP(99^99,--(0&MID(RC[-8],MIN(FIND({0,1,2,3,4,5,6,7,8,9},RC[-8]&1234567890)),ROW(INDIRECT("1:"&LEN(RC[-8])+1)))))

However, I got 0.0417.... instead. I do not know how to edit the equation though because for some of the rows it works fine but some doesnt.

Thank you in advance!

In addition, I realise i only have this problem when the subsequent letter is A, other letters are fine.
eg. 1 A or 2 A
but if it is 1 T or 2 T, the results is consistent

Thank you!