*The tutorial shows how to extract number from various text strings in Excel by using formulas and the Extract tool.*

When it comes to extracting part of a text string of a given length, Excel provides three Substring functions (Left, Right and Mid) to quickly handle the task. When it comes to extracting a number from an alphanumeric string, Microsoft Excel provides… nothing.

To get a number from a string in Excel, it takes a little ingenuity, a bit of patience, and a bunch of different functions nested into each other. Or, you can run the Extract tool and have the job done with a mouse click. Below you will find full details on both methods.

When you have a column of alphanumeric strings where number always comes after text, you can use one of the following formulas to get it.

To extract number from a 'text-number' string, the first thing you need to know is where to start the extraction. So, let's determine the position of the first digit with this generic formula:

MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},*cell*&"0123456789"))

We will dwell on the formula's logic a bit later. For now, simply replace *cell* with a reference to the cell containing the original string (A2 in our case), and enter the formula in any empty cell in the same row, say in B2:

`=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))`

Although the formula contains an array constant, it's a regular formula completed in the usual way by pressing the Enter key.

Once the position of the first digit is determined, you use the RIGHT function to extract the number. To find out how many characters should be extracted, you subtract the position of the first digit from the total length of the string, and add one to the result because the first digit is also to be included:

`=RIGHT(B2, LEN(A2)-B2+1)`

Where A2 is the original string and B2 is the position of the first digit.

The following screenshot shows the results:

To eliminate the helper column containing the position of the first digit, you can embed the MIN formula directly in the RIGHT function like this:

`=RIGHT(A2,LEN(A2) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) +1)`

To force the formula to return a **number** rather than a numeric string, nest it into the VALUE function:

`=VALUE(RIGHT(A2,LEN(A2) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) +1))`

We supply the array constant {0,1,2,3,4,5,6,7,8,9} in the *find_text* argument of the SEARCH function, so that the formula searches for each element of the array within the original string and returns their positions. Because the array constant contains 10 digits, the resulting array also contains 10 elements.

The MIN function processes the resultant array and returns the smallest value, which corresponds to the position in of the first digit in the original string.

Additionally, we use a special construction (A2&"0123456789") to concatenate every possible number with the original string. In situations when a certain number in the array constant is not found within the source string, this construction acts like IFERROR forcing the formula to return a "fake" position equal to the string length +1 or more chars. As the result, if the original string does not contain any number, like in row 7 in the screenshot above, the RIGHT formula returns an empty string.

To make things easier to understand, let's see how the formula works out for a specific cell, say A2. That cell contains the text string "ECDAA-05", for which the SEARCH function returns the following array {7,10,11,12,13,8,15,16,17,18}. Here's how:

- 0 is the 1
^{st}element of the array constant and the 7^{th}character in the original string, therefore the first item of the resulting array is "7". - 5 is the 6
^{th}element of the array constant and the 8^{th}character in the original string, so the sixth item of the resulting array is "8". - No other item of the array constant is found in cell A2, and therefore the other 8 items of the resulting array represent the positions of corresponding digits in the concatenated string (ECDAA-050123456789).

Since 7 is the smallest value in the resulting array, the MIN function returns it, so we get the position of the first digit (0) in original string.

Another way to extract number from the end of a string is by using this generic formula:

RIGHT(*cell*,SUM(LEN(*cell*) - LEN(SUBSTITUTE(*cell*, {"0","1","2","3","4","5","6","7","8","9"},""))))

With the original text string in A2, you enter the below formula in B2 or any other empty cell in the same row, and then copy it down the column:

**=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))**

In essence, the formula searches for all numbers from 0 to 9 within the source string, counts the found numbers, and returns that many characters from the end of the string.

And here is the detailed formula break down:

- First, you use the LEN and SUBSTITUTE functions to find out how many times a given number occurs in the string. For this, you replace the number with an empty string (""), and then subtract the length of the string without that number from the total length of the original string. This operation is performed on each number in the array constant.
- Next, the SUM function adds up all occurrences of all digits in the string.
- Finally, the digit count goes to the
*num_chars*argument of the RIGHT function instructing it to return that many characters from the right side of the string.

If you are working with strings where text appears after number, the formula for extracting number would be similar to the one discussed above, with the difference that you use the LEFT function to pull the characters from the left side of the string:

LEFT(*cell*,SUM(LEN(*cell*)-LEN(SUBSTITUTE(*cell*,{"0","1","2","3","4","5","6","7","8","9"},""))))

With the original string in A2, use the following formula to get number:

**=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))**

If your task implies extracting number from anywhere in a string, you can make use of the following mind-boggling formula published on MrExcel forum:

`=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)`

Where A2 is the original text string.

Breaking down this formula would require a separate article, so you can simply copy it to your worksheet to make sure it really works :)

Upon examining the results, however, you may notice one insignificant drawback - if the source string does not contain a number, the formula returns zero, as in row 6 in the screenshot above. To fix this, you can wrap the formula in the IF statement, the logical test of which checks if the source string contains any number. If it does, the formula extracts the number, otherwise returns an empty string:

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

As shown in the screenshot below, the improved formula works beautifully (kudos to Alex, our Excel guru, for this improvement):

Unlike in all previous examples, the result of this formula is **number**. To make sure of this, just notice the right-aligned values in column B and truncated leading zeros.

As you have just seen, there is no trivial Excel formula to pull number from a text string. If you have difficulties with understanding the formulas or tweaking them for your data sets, you may like this simple way to get number from string in Excel.

With our Ultimate Suite added to your Excel ribbon, this is how you can quickly retrieve number from any alphanumeric string:

- Go to the
*Ablebits Data*tab >*Text*group, and click**Extract**:

- Select all cells with the source strings.
- On the Extract tool's pane, select the
**Extract numbers**radio button. - Depending on whether you want the results to be formulas or values, select the
**Insert as formula**box or leave it unselected (default).My advice is to select this box if you want the extracted numbers to update automatically as soon as any changes are made to the source strings. If you want the results to be independent on the original strings (e.g. in case you plan to remove the source data at a later point), then do not select this box.

- Click the
**Insert Results**button. Done!

Like in the previous example, the results of the extraction are **numbers**, meaning you are free to count, sum, average, or perform any other calculations with them.

In this example, we've chosen to insert the results as **values**, and the add-in did exactly what was asked for:

If we selected the *Insert as formula* checkbox, you'd observe a **formula** in the formula bar. Curious to know which one? Just download Ultimate Suite's trial and see for yourself :)

If you'd like to have this as well as 60+ more useful tools in your Excel, be sure to grab the **15% off** coupon code that we provide exclusively to our blog readers: AB14-BlogSpo.

I thank you for reading and hope to see you on our blog next week!

Excel Extract Number - sample workbook

Excel formulas
CSV
Excel functions
Print
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 208 Responses to "How to extract number from string in Excel"

Hey,

Svetlana Cheusheva

Please tell me how I can make a live input cell that is

"Underlying Index: NIFTY 11907.30 As on Jul 03, 2019 10:05:20 IST" in B1 CELL

to another cell say in C1 11907.30 and time in C2 10:50:20 to another cell.

or at least I can print C1 that would be also sufficient.

mainly viewed all the comment but not able to figure the amount

This was immensely helpful, thanks. How do I get it to preserve any leading zeroes?

I meant, how do I get the last method (extract from anywhere) to preserve leading 0's? Thanks.

Hi,

This is very helpful.

One thing, the SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"") doesn't seem to be working for me. It is not replacing the characters with empty string.

Data in cell A2: 25R

Expected: R

This is a great post.

thank you so much for your effort here. I wish my company wasn't so tight in the pockets so I could get all those add-ins

Ram mobile no-9925923457. Resides In Noida 119961

Can You suggest How To find Phn no. From Above Text

Client Name

LALITA

GEETA DEVI NAYAK

MEHARUN NISHA

DIPA MANOJ

PREETI SINGHAL

meena devi swami

RAJIYA BEGAM

SHEHIDE

TARAWATI

BHATERI DEVI

sheela devi

JANKI DEVI

SUNITA

ALKA KANWAR

JAITUN

POOJA DEBI

CHHOTI DEVI

VIMLA DEVI

manju devi tak

MANJU

MUNNI DEVI

GEETA DEVI

TULSI DEVI

AILARAKHI

MUMTAJ BIBI

How to Find MID name if mid name more than 3 Character

=IF((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))>1,MID(A2,FIND(" ",A2,1)+1,SUM(FIND(" ",A2,FIND(" ",A2,1)+1),(FIND(" ",A2,1)+1)*-1)),"")

The formula, of middle search doesnt fetch n give decimal place like 18.625

Not sure how to extract check no’s. From a text string having more that one set of numbers - see example below:

Brad James Company - Check - 23897 / invoice # 456755 issued Sept 1

Any suggestions are greatly appreciated - the line above is a sample of the excel items and after the check number shown there a number of invoice numbers in the text string.

Thanks , Fred

If this is the data :

My Assumption is that all your data has "/" after the check number.

First :

Find the nth place of that "/" in that string using this formula :

=+FIND("/",D2)

Brad James Company - Check - 23897 / invoice # 456755 issued Sept 1

Output = 36

then,

use this formula =+MID(D2,C2-7,7)

here D2 is the input data which you have & C2 refers to the output of find formula i.e)36

then the output will be "23987".

Hope this helps! :)

This formula is exactly what I was looking for... and it works perfectly. Thank you so much! ;-)

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

Hey,

Svetlana Cheusheva

Thank you so much for the tutorial. It is very nice of you. It was very helpful to me.

While this was very very helpful, I am facing a new kind of problem.

Job Id - #2416387528594195 is getting converted to '2416387528594190, excel is rounding off the last two digits and replacing with 0. Concat with an apostrophe also didn't help. I applied formula: =CONCAT("'",SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))*ROW(INDIRECT("1:"&LEN(B2))),0),ROW(INDIRECT("1:"&LEN(B2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B2)))/10))

This formula does not give the decimal values i.e 5.25, 7.3 and more. Kindly help me out on this.

Formula ,

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

=SUM(MID(0&A3,LARGE(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3)))*ROW(INDIRECT("1:"&LEN(A3))),1)),ROW(INDIRECT("1:"&LEN(A3))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A3)))-1))

not getting any result if help if there is any error

Thanks for the formulas (But I had trouble get them working. Here is why)

In some countries, Sweden among them, the "," character is a decimal delimiter. There for "SEARCH({0,1,2,3,4,5,6,7,8,9},A2)" results in an error. So for us we have to use another character in the syntax, ";". So here is what worked for me:

SEARCH({0;1;2;3;4;5;6;7;8;9},A2)

Hope above saves some time for others!

The article was such Helpful that I got rid of a work that could have required me to dedicate lots of minutes. Thank you all.

How can add the numbers of this following examples, without sorting it fisrt

CIVUS0.35B

CIVUS1W

IA-CIVUS0.13W

TAVUS0.35SB

AVSSF2W/B

AVS3W/B

hi i have a cell from vendor quotes that i receive. this is how i name the vendor quote.

Lighting Expression 11-20-19 ($132,833)

Vendor name - date provided - dollar amount

i want to extract only the dollar amount which can vary up to 2 million.

Below formula working fine but some number have percent sign between the text,i want extract number with percent sign which have % Sign as well.

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

What is a good formula to use in order to see numbers that are alike in any order? For example

457. 885

886. 275

573. 547

Thanks for this great solution. The formula given above ("How to get number from any position in a string")-> is work fine.

Hello, what formula can I use to pull and separate the last four sets of numbers in the following string?

"CJFRO20190047 000 004 03/14/19 1906 JP MORGA JPMC 1st Qtr 0.00 0.00 0.00 -12.63"

I used the first formula from above but it doesn't give me all the values.

Any advice will be greatly appreciated.

What would be the number if the invoice number has alphanumeric characters in the middle.

Example:

09187GH1234

Hi How do I find the MAX numerical value of the alphanumeric string? for example:

X-0100

B-0213

F-0505

Z-0111

to show that F-0505 is the high value in the column

Hello Kevin!

If I understand your task correctly, the following formula should work for you

=INDEX(A1:A5,MATCH("*"&LARGE( --RIGHT(A1:A5,LEN(A1:A5) - FIND("-",A1:A5)),1),A1:A5,0))

If there is anything else I can help you with, please let me know.

Hi, could someone please help?

trying to extract the size from the following:

KIERRASTONE ASH TEXTURED ZKI2655A 300 X 600 X 9MM

(300 X 600 X 9MM)

i dont want the numebrs with the text (zki2655a) only the 300 X 600 X 9MM

thanks look forward to your reply :)

Hello Peter!

If I understand your task correctly, the following formula should work for you:

=MID(A1,FIND("(",A1,1)+1,LEN(A1)-FIND("(",A1,1)-1)

I hope this will help, otherwise please do not hesitate to contact me anytime.

how can i extract set of 6 number form the below string

"LBS 28 Marg, Bhandup West, Mumbai 400078, Maharashtra"

Hello Vinay!

To extract all numbers from text, use the formula

=CONCAT(IF(ISNUMBER( --MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),""))

I hope it’ll be helpful.

B264 80 0172760 STAINLESS STEEL HEX HEAD BOLT ASSEMBLY, 1/2" X

What if I'm needing to pull only a seven digit number out of a string of text. I only need the 0172760 from the text. The problem I'm having is that the position of this seven digit number is not consistent from cell to cell and the previously mentioned formulas don't apply because they pull all digits, not just ones with a certain length.

what is the appropirate formula to find mid value (i.e. 602969) of FP:ADBL5-602969-2830 starting from "FP" among the spread sheet.

Hello!

If I understand your task correctly, please try the following formula:

=MID(A1,FIND("-",A1,1)+1, FIND("-",REPLACE(A1, FIND("-",A1,1),1,""),1)+1 -FIND("-",A1,1)-1)

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hi All,

Can you please help me extract this six digit number.

clg:ramanlal/chennai/012345/April

hello Nitin!

To extract a 6-digit number from a mext, use the formula

=MID(A1,MATCH(0, --ISERROR(-MID(A1,ROW($1:$99),1)),),6)

I hope it’ll be helpful.

-6.135474.10.00.100012-AziziDevelopments-WO-1-73944857464-CONTR0067799835-Inet

I want to extract only this portion "6.135474.10.00.100012" and some thing like that number from rest of data of 3000. Can anyone help me please with the formula.

THANK YOU SO MUCH THIS SAVED MY LIFE

hi,

Eg: One column 20Pcs Disposable Filter 3 Ply mask and another column 20

how to find the same number exist in that string is correct?

I have tried SEARCH option but is show only position. i want the exact number found in both the column is right/wrong?

Hello!

It is not clear what result you want to get. But maybe this formula is right for you.

=IF(SEARCH(B1,A1,1)>0,"Right","Wrong")

Hope this is what you need.

thank you so much..

Hi Team,

-6.135474.10.00.100012-AziziDevelopments-WO-1-73944857464-CONTR0067799835-Inet

I want to extract only this portion "6.135474.10.00.100012" and some thing like that number from rest of data of 3000. Can anyone help me please with the formula.

Hello Mayank!

If I understand your task correctly, the following formula should work for you

=LEFT(A1,SEARCH("-",A9,2)-1)

I hope this will help, otherwise please do not hesitate to contact me anytime.

Many Many Thanks Alexander. Will try to implement with this new formula.

This formula is working but the output is in form of exponential format. I am trying to remove GL code only, but it does not seem to be working.

Here is the example:

Resident Care:69000 · Wellness:69800 · Salaries and Wages:69890 · Payroll Taxes:69891 · FICA

Resident Care:69000 · Wellness:69800 · Salaries and Wages:69890 · Payroll Taxes:69895 · FUTA

Resident Care:69000 · Wellness:69800 · Salaries and Wages:69890 · Payroll Taxes:69897 · MI-UIA

Hello!

I’m sorry but your task is not entirely clear to me.

For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

I want o extract text from number like:

1. 100Rte02T------RTet

how can i do that by using formula

Hello Learner!

To extract all letters from text, use the formula

=SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER( --MID(A1,ROW($1:$93),1))), MID(A1,ROW($1:$93),1),"")))," ","")

Hope this is what you need.

very helpful but please make practice sample files available.

Hi!

You can find the practice sample workbook at the end of this tutorial under "Available downloads".

I am wondering if this formula can be applied for address street number extraction, wherein the address line you have multiple numbers. For example:

"Rua Hungria, 1240 – Jd. Europa | 1º andar" = 1240 & 1 = 12401

And so, I was hoping for a solution to insert "-" between every occurring number. Thoughts?

This is really great post! Thanks.

Hello Krystian!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you. Thank you.

Hey Alex!

I am using this formula:

=SUMPRODUCT(MID(0&I25, LARGE(INDEX(ISNUMBER(--MID(I25, ROW(INDIRECT("1:"&LEN(I25))), 1)) * ROW(INDIRECT("1:"&LEN(I25))), 0), ROW(INDIRECT("1:"&LEN(I25))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(I25)))/10)

And here is the example of raw data:

R LEOPOLDO COUTO DE MAGALHAES JUNIOR, 758 - ANDAR: 15; CONJ: 151;

Using the formula on the above example, I am getting this: 75815151 - concatenated numeric value of all numbers from the string. And, what I am hoping is to add a special character that would show the numbers like so: 758-15-151

In short, on top of extracting the numbers, differentiate multiple numbers by some special character, "-" for example.

Let me know if this is better. Thanks!

Hello Krystian!

You can use a custom format using the TEXT function

=TEXT(A1,"###-##-###")

where A1 is the cell with your formula.

Or use your formula in the TEXT function

=TEXT(SUMPRODUCT(MID(0&I25, LARGE(INDEX(ISNUMBER(--MID(I25, ROW(INDIRECT("1:"&LEN(I25))), 1)) * ROW(INDIRECT("1:"&LEN(I25))), 0), ROW(INDIRECT("1:"&LEN(I25))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(I25)))/10),"###-##-###")

I hope this will help, otherwise please do not hesitate to contact me anytime.

hi there,

how do i extract any number before a decimal point using a formula.

meaning 5569.9008 i only want to extract 5569. the formula has to be across for any types of decimals and combination numbers. thank you for the assistance.

Hello!

For decimal use the INT function

=INT(A1)

What is the combination numbers? Google does not know. Neither do I.

Dear

for example : 25,20,15,25,300,40 is it possible to extract the numbers before "g",

Ali Baba Dark Chocolate 25 gm box 12 pcs

Ali Baba Dark Chocolate 20gm*24 box

Cadbury 5 Star White Chocolate 15gm

Kinder 2 White Chocolate 25 gm*24

ALpella Biscuits W/Marshmallow300gm

Alpella Chocolate 40gm

plz let me know the formula

Hello!

If I understand your task correctly, the following formula should work for you:

=CONCAT(IF(ISNUMBER(--MID(MID(A15, FIND("g",A15,1)-5,5),ROW($1:$93),1)), MID(MID(A15,FIND("g",A15,1)-5,5),ROW($1:$93),1),""))

I hope it’ll be helpful.

Thanks for the formula - it works, and it's going to save me a ton of time!