Using SUMPRODUCT to Extract #'s from mid String including Zero's

[BlindAlley]

Afternoon Guys,

I am using the excellent SUMPRODUCT formula below to extract numbers from a scoreline provided by Web Query.
=SUMPRODUCT(MID(0&$AD$212, LARGE(INDEX(ISNUMBER(--MID($AD$212, ROW(INDIRECT("1:"&LEN($AD$212))), 1)) * ROW(INDIRECT("1:"&LEN($AD$212))), 0), ROW(INDIRECT("1:"&LEN($AD$212))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN($AD$212)))/10)

The final match results come in using this format: 'Liverpool 4 - 0 FC Barcelona' in a text string, the above formula returns the result 40 which works fine when I use the LEFT or RIGHT Function afterwards to extract the numbers. During testing however, if the result was reversed 'Liverpool 0 - 4 FC Barcelona' the formula returns a 4 without the leading zero making it impossible to determine the match winner.

So my question is, could the formula be modified to include leading zero's or is there a good work around that I haven't thought of ?

I tested this earlier btw
=MID(AD220,LOOKUP(10^99,--SEARCH(ROW(INDIRECT("1:900000")),AD220)),6) but run into the same problem 1st score is correct the reversed score is incorrect.

The test sheet supplied shows both correct and incorrect results.

Thanks in Advance.

[Ron Coderre]

Seems like more work than is necessay.
Using your posted workbook, I think these regular formulas do what you want:

Please Login or Register  to view this content.

Also, your winner calculation formula doesn't test for tie scores...
Maybe this?

Please Login or Register  to view this content.

Does that help?

[BlindAlley]

Thanks Ron,

Let me test those out and get back to you.

As for the 2nd formula there are no ties in the Champions league during the 2nd leg, they use the away goals count double, extra time, and penalties to negate a tie.

[BlindAlley]

Excellent - Works for me Ron thank you.
I've been scratching my head on this problem for two days and now it is fixed.

Thanks again
Steve