Help modify existing formula to extract number from a cell

[FONT=&quot]I have a sample of the following excel sheet.

I want basically the column A2 to return only the numbers. I have an existing formula. The only problem is if I look at Row 1,2 and 3 they start off from 1 instead of including the 0. I would like it retrieve any number even if they start with 0.

I have tried to change the format to say text but it does not help.

The formula I am currently using to extract only the numbers is

=IF(SUM(LEN(I27)-LEN(SUBSTITUTE(I27, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&I27, LARGE(INDEX(ISNUMBER(--MID(I27,ROW(INDIRECT("$1:$"&LEN(I27))),1))* ROW(INDIRECT("$1:$"&LEN(I27))),0), ROW(INDIRECT("$1:$"&LEN(I27))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(I27)))/10),"")

I am using Excel 2010

Thank you in advance.


[/FONT]
Quote Reply

Provide a sample workbook.

Please find attached the sample workbook.

Thank You.

If you have the TEXTJOIN worksheet formula available to you then try in cell B2:
copied down.

If you don't, then try array-entering (using CTRL+SHIFT+ENTER to commit the formula to the sheet instead of the more usual plain ENTER) this formula in cell B2 instead: