Extract a numerical value after a text.

Good afternoon all,
I tried to reply to a previous posr but did not see my reply, so I started a new one. Sorry if this is problem. Now my problem. I have the following text:
Padstack: 65R45D Type: through Inner pads: Optional
Padstack: 75S45D Type: through Inner pads: Optional
Padstack: 115R45D Type: through Inner pads: Optional.
I want to extract the number after the space following the ":". So the first would return 65, the second 75, and the third 115. Can this be done with a formula and if so could someone please help. I have been searching for an answer for 5 hours and had some luck with the following which I found, =IFERROR(LOOKUP(1E+99,LEFT(MID(A5,LOOKUP(1E+99,SEARCH(ROW($1:$9999)&"_",A5)),9),ROW($1:$9))+0),"WRONG"), but in the 1st and 2nd example there can be a different letter between the numbers it will not work. This is why I would like to use the space, as it does not change.

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre

That is an awesome equation. Hats off to Ron.

I would have expected that it had to be entered as an array. I am impressed. layers upon layers.

Originally Posted by ChemistB

That is an awesome equation. Hats off to Ron.

+1 to this

Originally Posted by Ken64

Thank you very much. I don't know how you guys do this stuff. I can't wrap my head around it.

With that formula, nor do I...I just know it works, and I know is a genius

Here's what's happening;

Formula:

Please Login or Register  to view this content.

This is searching the string for the first occurance numbers 0 through 9 (0123456789 is appended to the end so there's always a result so no errors are generated) and returns their positions
and then MIN narrows that down to the very first position (which in this example is 11)

Replacing 11 for that portion of the equation

Formula:

Please Login or Register  to view this content.

generates from A1 starting at position 11 progressively strings of more characters. (6, 65, 65R, 65R4,65R45, 65R45D ....)
When the "LOOKUP(99^99" cannot find a matching number in that progression (because 99^99 is so large) it returns the last true number it finds which (in this case) is 65.