How to Extract the Number from The Text In Excel

Select a blank cell that is adjacent to the list you want to extract number only, and type this formula =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10) (A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula.

See the Screenshot

It might be confusing to you so here is the alternative

Downlod The Addins Number Extractor

It has more features and simple formula 

It has Two Functions: 

1.     =TONUMBER(MIXEDTEXT,INDEX)

eg: TONUMBER("asd1.25s3e") Gives 12

It Extracts the Number With Out Decimal 

MIXEDTEXT is the number mixed text.

INDEX is the nth number which you want to extract it is by default "1" Such as if you keep it 1 it will give the first number (eg: "56" from text  "sds5.6dfe955sd")  in the text and 2 gives second number (eg: "955" from text  "sds56dfe955sd") in the text.

Note:  to extract all number as one Give the Index as 0 EG: TONUMBER("sds5.6dfe955sd",0) Gives  56955

2.     =TONUMBERDECIMAL(MIXEDTEXT,INDEX)

eg: TONUMBERDECIMAL("asd1.25s3e") Gives 12

It Extracts the Number With Decimal 

MIXEDTEXT is the number mixed text.

INDEX is the nth number which you want to extract it is by default "1" Such as if you keep it 1 it will give the first number (eg: "5.6" from text  "sds5.6dfe955sd")  in the text and 2 gives second number (eg: "955" from text  "sds5.6dfe955sd") in the text.

Note:  to extract all number as one Give the Index as 0 EG: TONUMBERDECIMAL("sds5.6dfe955sd",0) Gives  5.6955

Features:

1. Extracting Whole number as a single number Such as  1056896 from K-105d6s8fg96 Using the second parameter INDEX as 0
2. Extracting the Number different numbers seperately Such as "105"  in one cell "6" in another Cell and "96"  in another Cell from K-105d6s8fg96 Using the second parameter INDEX