Using your posted workbook,
this regular formula returns the number from the end of the text
E15: =IFERROR(LOOKUP(99^99,--RIGHT(B15,ROW(INDIRECT("1:"&LEN(B15))))),"")
Is that something you can work with?