Extract numbers from text string

I am trying to write a formula to extract the number from a cell with a text string (containing the number). Besides not knowing how to do this, the complication is that the numbers are not always 4-digits and I don't want the "-n" part of the number. I appreciate anyones help. The attached file "Test1" has example data in sheet 1. The data is in column B and the desired result is in column C.

Thanks AlKey. This worked except I found there are some text strings that had only 2 digits which returned a #value since the 4 digits asked for in formula included letters. Adding the +0 forces it to be a number which didn't like the letter in the equation. If I took the +0 out, it did give me the 4 digits found correctly.

Thanks FDibbins. This did it spot on. Even when I found cells in the array that had only 2 digit numbers. I am impressed that it truncated the text after the 2 digit numbers. Will you explain how this works (for my education)? I don't understand all parts of the formula.

Hi,

Please explain this formula, to understand the concept.

=MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&1234567890)),4)+0

Formula:

Please Login or Register  to view this content.

Hi
I am looking for a way to search all text within each row within one column and find the exact match in another workbook and highlight that cell with respective average. overall and actual date wise.

I have attached a sample of what I am trying to do.

Any help would be greatly appreciated as I have drawn a blank here!

Regard's,
Arijit