Excel formula to pick only number from text

[udaykundapur]

Hi Experts,

I want a excel formula which picks only number from text. I have a column contains only numbers (456) and numbers and text (NUM 123), but I wanted only nubmers in the another cell. Please suggest.

Tickets
NUM 123 shoul display as 123
456 should display as 456
NUM 678 shoule display as 678

 

[bbott]

How about:

=IFERROR(RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)+0

or you could do a find and replace. Find value would be * followed by a space. Replace value would be empty.

 

[Aladin Akyurek]

In B2 enter and copy down:

=IF(ISNUMBER(1/A2),A2,REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")+0)

 

[billszysz]

Are there always "NUM " in cells with number and text?
Whether the text is always at the begining?
How long are your numbers (3 digits, 4 digits, with decimals or without?)
If there is always "NUM " then we can use simple formula.

=--SUBSTITUTE(A1,"NUM ","")