How to Extract the Number from The Text In Excel

How to Extract the Number from The Text In Excel

Have you wondered how to extract the Number in Excel From the Text

Have you ever wanted to extract numbers only from a list of string in Excel? Here I introduce some ways for you to extract only numbers quickly and easily in Excel.

 

1. Using formula is an easy way for most of Excel user (Limited).

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

Number Extracting

It might be confusing to you so here is the alternative

Using The Simple Excel Addins of Only 24 KB

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


File Size: 22.12kb
application/vnd.ms-excel.addin.macroEnabled.12

Now Check This Out

;