Formula to pick a number out of a text string in Excel

I've been working on a formula to pick a number out of a text string.  Unlike all of the other solutions I've found on the web, I want to find a positive OR negative number, integer or floating point.  I've got it really close.  It basically picks the number with the largest absolute value out of the array consisting of every possible substring within the string.  The only problem is that if I have a string like 65yds-67.890g%4grj4 it will find the 890 as that is a valid number.  I would like it to return the -67.890 so what I really want is to find the longest string that is a valid number.

This is the first formula I had that works pretty well, it just has the aforementioned problem...

=IF(MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0)>ABS(MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1))),MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0),MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1)))

Select allOpen in new window

It's an array formula, so you have to enter it with Ctrl+Shft+Enter.

This formula finds the length of the longest string that is a valid number, I just haven't been able to pull out the string itself.

=LARGE(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),LEN(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1,""),1)

Select allOpen in new window

What's killing me is that functions like MATCH and VLOOKUP won't find values in a two-dimensional array.

I don't want to write a custom function in VBA.

Is there an expert out there that can help me finish my masterpiece?

For people who can't stand the code snippet box, here's the first formula in raw form...

=IF(MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0)>ABS(MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1))),MAX(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),0),MIN(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1)))


And here's the second.

=LARGE(IF(ISNUMBER(VALUE(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1),LEN(MID(A2,COLUMN(INDIRECT("a1:" &  ADDRESS(1,COLUMN(OFFSET($A$1,0,LEN(A2)))-1),1)),ROW(INDIRECT("1:" & LEN(A2)))))*1,""),1)