Excel formula to check if a cell has number and return that number

[lankan12]

Hi Guys,

I wanted to know if there is an excel formula that can be used to return the value of a number in a certain cell for example

I have a1 = Buy 1240 Donuts - $450.00
Now on B1, I want a formula which can return the value 1240.

Thank you guys!

 

[Scott T]

If the number is always second in the text string this should work. If it is not a number this will return blank.

=IF(ISERROR(TRIM(MID(A1,SEARCH(" ",A1),SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1)))+0),"",TRIM(MID(A1,SEARCH(" ",A1),SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1)))+0)

 

[Rick Rothstein]

If as Scott said, the number is always second in the text string, then here is another formula you can try...

=IFERROR(0+TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99)),"")