Formula that finds and returns numbers from a cell containing a mix of text and numbers

Hi,

So far, I managed to find a formula that can do this, however, if the cell contains characters such as "-" it will return #VALUE error. This is the formula:

=1*MID(F20,MATCH(TRUE,ISNUMBER(1*MID(F20,ROW($1:$9),1)),0),COUNT(1*MID(F20,ROW($1:$9),1)))


It will return 1234 from cell F20 which contains abc1234.

How can I change the formula so that it returns 1234 even if cell F20 contains abc-1234?

Thanks

If the number is always going to be at the end of the string then this should work:

=MID(A1,MATCH(TRUE,INDEX(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),0),0),LEN(A1))

Or, if you fancy a non-volatile approach:

=RIGHT(A1,MAX(IF(ISNUMBER(VALUE(RIGHT(A1,255-ROW(1:254)))),255-ROW(1:254))))

That's an array function, so must be entered with Ctrl-Shift-Enter, not just Enter.