by 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 numbers 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.
How to extract number from the end of text string
When you have a column of alphanumeric strings where number comes after text, you can use the following formula to get it.
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:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))
This formula gets number only from the end. If a string also has numbers in the beginning or middle, they are ignored:
The extraction is performed with the RIGHT function that belongs to the category of Text functions. The output of this function is always text. In our case, the result is a numeric substring, which in terms of Excel is also text, not number.
If you need the result to be a number (that you can use in further calculations), then wrap the formula into the VALUE function or perform an arithmetic operation that does not change the result, say, multiply by 1 or add 0. To catch errors in the strings that do not contain a single number, use the IFERROR function. For example:
=IFERROR(VALUE(RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))), "")
or
=IFERROR(RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0))) +0, "")
Note. In Dynamic Array Excel (Office 365 and 2021), you enter the formula in the usual way with the Enter key. In Excel 2019 and earlier, it only works as an array formula, so remember to press Ctrl + Shift + Enter to complete it.
How this formula works:
To extract number from an alphanumeric string, the first thing you need to know is where to start the extraction. The position of the last non-numeric character in a string is determined with the help of this tricky formula:
MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0))
To understand the logic, let's investigate it from the inside:
The ROW(INDIRECT("1:"&LEN(A2))) combination creates a sequence of numbers corresponding to the total of characters in the source string (A2), and we serve these sequential numbers to MID as the starting numbers:
MID(A2, {1;2;3;4;5;6;7;8}, 1)
The MID function pulls each individual character from A2 and returns them as an array:
{"0";"5";"-";"E";"C";"-";"0";"1"}
As MID is a text function, its output is always text (as you can notice, all the characters are enclosed in quotation marks). To turn numeric ones into numbers, we multiply the array by 1 (double negation --MID() will have the same effect). The result of this operation is an array of numbers and #VALUE! errors representing non-numeric characters:
ISNUMBER({0;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0;1})
The ISNUMBER function evaluates each element of the array and gives its verdict in the form of Boolean values - TRUE for numbers, FALSE for anything else:
{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}
This array goes to the logical test of the IF function, where each element of the array is compared against FALSE:
IF({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)
For each FALSE (non-numeric value), another ROW(INDIRECT()) function returns its relative position in the string. For each TRUE (numeric value), a zero is returned. The resulting array looks as follows:
{0;0;3;4;5;6;0;0}
The rest is easy. The MAX function finds the highest number in the above array, which is the position of the last non-numeric value in the string (6 in our case). Simply, subtract that position from the total length of the string returned by LEN, and pass the result to RIGHT to let it know how many characters to extract from the right side of the string:
RIGHT(A2, LEN(A2) - 6)
Done!
How to extract number from the beginning of text string
If you are working with records where text appears after number, you can extract number from the start of a string by using this generic formula:
With the original string in A2, use the following formula to get number:
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)
No matter how many digits are in the middle or end, only the starting number is extracted:
Note. In Excel 365 and Excel 2021, due to support for dynamic arrays, a regular formula works fine. In Excel 2019 and earlier, you should press Ctrl + Shift + Enter to explicitly make it an array formula.
How this formula works:
Here, we again use the combination of ROW, INDIRECT and LEN functions to create a sequence of numbers equal to the total of characters in the source string plus 1 (the role of that additional character will become clear a bit later).
ROW(INDIRECT("1:"&LEN(A2)+1))
MID and ISNUMBER do the same job as in the previous example - MID pulls individual characters and ISNUMBER converts them to the logical values. The resulting array of TRUE's and FALSE's goes to the MATCH function as a lookup array:
MATCH(FALSE, {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}, 0)
MATCH computes a relative position of the first FALSE, giving us the position of the first non-numeric character in the string (3 in A2). To extract the preceding numbers, we subtract 1 from position the first text character and serve the difference to the num_chars argument of the LEFT function:
LEFT(A2, 3-1)
Now, back to an "extra" character in the sequence generated by ROW(INDIRECT()+1)). As you already know, this sequence provides the starting points for the MID function. Without +1, MID would extract exactly as many characters as there are in the original string. If the string contains only numbers, ISNUMBER will return only TRUE's while MATCH needs at least one FALSE. To ensure that, we add one more character to the total length of the string, which the MID function would convert to an empty string. For example, in B7, MID returns this array:
{"1";"2";"3";"4";""}
Note. As is the case with the RIGHT function, LEFT also returns a numeric substring, which is technically text, not number. To get the result as a number rather than a numeric string, nest the formula in the VALUE function or multiply the result by 1 as shown in the first example.
How to get number from any position in a string
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.
Tip. In Excel 365 - Excel 2019, there is a much simpler solution with the help of the TEXTJOIN function. Please see How to remove text and keep numbers.
Extract number from text string with Ultimate Suite
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 the Insert as formula checkbox was selected, you'd observe a formula in the formula bar. Curious to know which one? Just download Ultimate Suite's trial and see for yourself :)
Available downloads
Excel Extract Number - sample workbook (.xlsx file)
Ultimate Suite - trial version (.exe file)
565 comments
Good day! I am trying to extract and add the number of pages in each line of the following example:
(1 x Stapled): 1 x Ldgr/C
(1 x Stapled): 4 x Ltr/D
(1 x Stapled): 17 x Ldgr/C
(1 x Stapled): 26 x Ldgr/C/D; 1 x Ldgr/C
(1 x Stapled): 16 x Ldgr/C/D; 1 x Ldgr/C
(2 x Stapled): 32 x Ldgr/C/D; 2 x Ldgr/C
(1 x Stapled): 9 x Ltr/D; 7 x Ltr/C/D; 1 x Ltr
(1 x Stapled): 1 x Ltr/C
Might you be able to assist with a formula? Much appreciated!
Hi!
Sorry, it's not quite clear what you are trying to achieve.
What does the “(1 x Stapled): 1 x Ldgr/C” phrase mean?
Describe in detail what problem you have, and I will try to help you.
Hi Alexander! Thanks so much for the quick reply. This is data from our photocopier usage. Each line is a print job; the first number is how many staples were used, followed by how many pieces of paper, letter (ltr) or ledger (ldgr))size. I'm trying to extract the number of pages printed for each line and add them together.
I hope that helps! Please let me know if you need further details.
Hi!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:
=--MID(A1,SEARCH(":",A1,1)+1,SEARCH("x",A1,SEARCH(":",A1,1))-SEARCH(":",A1,1)-1)
You can also use Split Text tool with mask
*:*x*
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Thanks so much! This is sooo close to what I'm looking for. :) I'm sorry I'm not explaining it very well...
If you look at this line:
(1 x Stapled): 1 x Ltr/C/D; 7 x Ltr/D; 1 x Ltr
The result I'm looking for is "9".
I hope that makes it more clear! You are certainly a wizard. Thank you so very much for your time and assistance!
Hello!
In the text
(1 x Stapled): 1 x Ltr/C/D; 7 x Ltr/D; 1 x Ltr
there are numbers 1 1 7 1
Which ones do you sum to get 9?
Hi! The first number is the number of staples, so I do not want to add that one; just the three following numbers which are number of pages used. :)
Hope that helps! Thanks so much!
Hello!
If I got you right, the formula below will help you with your task:
=SUM((IF(ISNUMBER(--MID(MID(A1,14,100),ROW($1:$100),1)),--MID(MID(A1,14,100),ROW($1:$100),1),"")))
I hope my advice will help you solve your task.
Hi, how to extract numbers in middle of Unicode strings, example "2 – 이 (i)"? Thanks Ahead!
5KM (1) Back to Basics
>21KM Sky's the Limit
Hi, I'm amazed by Sir Alexander's superb excel skills. I'm just trying out my luck here hopefully sir can solve my problem. I would like to extract only the number '5' and '21'. I wonder it is possible. Thank you.
Hello!
Press CTRL + H. In the "Find what" field, write (*). Do not write anything in the "Replace with" field. Click "Replace".
Then use the formula from this article and comments. For example:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))
I hope I answered your question.
Hi,
I have a spreadsheet of thousands in the following format
1. zvsnsnshs 2020DDE542134
2. sgenemene2020SHB6721
3. reenmennee 2020RTY409
I want to extract 2020DDE542134 in 1, 2020SHB6721 in 2 and 2020RTY409 in 3
2020 is followed by three letters but the number of digits thereafter vary.
Please assist.
Hello!
The formula below will do the trick for you:
=MID(A1,SEARCH("2020",A1,1),100)
I hope it’ll be helpful.
I use this to get numbers only from mixed Alpha Numeric strings
TEXTJOIN(“”,TRUE,IFERROR(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1,””))
Amazing formula and explanation. Thank you very much!
Hello,
How can i remove last numeric digit from a text numeric string e.g.
abc, adi, 23 fhve sihf ghr 98000
Thank you for this amazing formula!
=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)
It works almost perfectly, however I would like to separate the different number sets with a space.
e.g.
returned value with formula: 5468751013
desired return value: 546875 1 0 13 (number sets vary)
Regards
Donald
Hello!
If you want to extract groups of numbers from the text and separate them with a space, use this formula:
=SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A2,ROW($1:$94),1)),MID(A2,ROW($1:$94),1)," ")))," "," ")
Hope this is what you need.
Wow! That worked perfectly!
Greatly appreciate this support!
I find this thread quite amazing.
What I offer below may be a solution to a one-off problem as no-one else describes anything similar. But perhaps I am not the only one so here goes.
When I scraped a table from the following site:
https://en.wikipedia.org/wiki/List_of_countries_by_net_migration_rate
- I found columns of numbers displayed in Excel as either regular positive numbers, aligned right, or negative numbers as text, aligned left. I tried all the techniques recommended for converting text to number but nothing worked. So in frustration I converted them all manually, hardly a practical solution for a large database.
Then it dawned on me that it must be the actual "-" character which was causing the problem. I discovered there are online Unicode character identifiers, one of which I used to identify how these negative characters were different. The "-" for a regular negative number identified as "U+002D : HYPHEN-MINUS {hyphen or minus sign}". The problem character identified as "U+2212 : MINUS SIGN". When I used Ctrl-H, find and replace-all in Excel, all the negative text numbers instantly converted to regular numbers, aligned right, no further action needed.
BR_GID/908764_JK2
what is the formula to get only 908764 number .
Hi,
Please check the formula below, it should work for you:
=LEFT(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)), MID(A1,ROW($1:$94),1)," ")))," ","-"), SEARCH("-",SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)), MID(A1,ROW($1:$94),1)," ")))," ","-"),1)-1)
I hope it’ll be helpful.
Hi,
I have a spreadsheet of a couple of thousand lot plans in the format of
103SP122202
10SP133260
1RP43701
They are always numbers followed by letters followed by numbers.
I am looking for a formula to return all the numbers before the first letter and place in a column
103
10
1
Then a formula to return all the letters, and the numbers after the letters to place in another column
SP122202
SP133260
1RP43701
how would i achieve this?
Hello!
Write your value in cell A1. To extract the text, write the formula in B1.
=SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER(--MID(A1,ROW($1:$94),1))), MID(A1,ROW($1:$94),1),"")))," ","")
To extract the first number, write the formula in C1.
=LEFT(A1,SEARCH(B1,A1,1)-1)
To extract the second number, write the formula in D1.
=RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)-1)
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi Alexander,
Thank you very much for replying with a solution, it works very well.
Is it possible to alter =RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)-1) to keep SP122202 together in a cell and not separated?
Regards
Andrew
Hi,
Please try the following formula:
=B1&RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)-1)
or
=RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)+1)
Hi,
The second formula worked perfectly,
Thank you very much.
Regards
Andrew
I've been an advanced Excel user for 10+ years. It's pretty rare these days but when I hit a wall I eventually search for something online and I'm amazed, how many of those times I've ended up on one of Svetlana's posts, like 50+. I'm a big fan of hers! Joshua
Hi Team,
I want below number to be extract from below given sentence.
Q. 1 : "GL CASH DIPOSITED DONE BY ONE DATE IS 05/10/2020 02771600241 [AccountID: 123456767"
I want the answer should be "02771600241"
Q2: CASH DEPOSITED IN TRANSACTION ID - 02801900536 [AccountID: 1257895333 Account Name: Cas
I want the answer should be " 02801900536 "
Hi,
If I got you right, the formula below will help you with your task:
=TRIM(RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH(" [",A1,1)),"-",REPT(" ",20)),20))," ",REPT(" ",20)),20))
What if I have multiple potential phone numbers in a free-form field and want them to extract, but be separated by a delimiter? Working from a DB extract where the most useful phone numbers are entered free-form with other miscellaneous tidbits like this:
UserName 1234567890 Location 0987654321 OtherInfo
OtherInfo 1234567890 Location
M 1234567890 C 0987654321
Location 1234567890 0987654321
While your formula works great for extracting the numbers, it's resulting in strings like this now 12345678900987654321, which I would then need to split back up. Not all #s are 10 digits. Some have only 7 and others are international.
Hello!
You have not written what result you want to get. Therefore, I can only recommend this article - Custom Excel number format.
If something is still unclear, please feel free to ask.
Thanks for the formula, its really helpful and time saving
Hi,
If i need to do the sum of the below 2 columns which has number and a percentage in same cell, how do i do this?
Could you please help. Thanks a lot.
4 (100%)
5 (83%)
Hi,
What result do you want to get exactly? Your question is not entirely clear, please specify.
Thank you for replying.
COlumn A Column B
Total test runs Total test executed
4 4 (100%)
6 5 (83%)
------------------------------------------------------
10 9 (91.66%)
In column B, I want to get the total no of executed test cases ( 4+5). Also I want to get the average percentage of column B v/s Column A total count.
Hello!
To convert text string "4 (100%)" to number 4 use the formula
=--REPLACE(B2,SEARCH("(",B2,1),10,"")
I hope it’ll be helpful.
Hi Alex,
Problem description
TXT_TXN_DESC Required field
NEFT Cr-UTIB0000231-SIVA C-JANA SMALL FINANCE BANK-AXIR210011779565 AXIR210011779565
RTGS Cr-HDFC0000240-LIGHTMICROFINANCEPVTLTD-JSFBCollectionAccountMSE-HDFCR52021010166833120 HDFCR52021010166833120
NEFT SBIN521001912911-Mrs GOPA BHATACHARJEE-JANA SMALL FINANCE BANK LTD SBIN521001912911
30768647394421 KKBKR52021010200888475 KKBKR52021010200888475
33598650000698 20210102 IOBAN21002635205 IOBAN21002635205
30098850001352 P002210081154581 20210102 P002210081154581
What formula will work for to segregate an UTRNs to new column, please suggest.
Hi,
What do you want to calculate exactly? Your question is not entirely clear, please specify.
Is this text from one cell or from several? How do you want to split it? Give an example.
Hi,
I have used your formula above for extracting numbers from the left of a string [=LEFT(C738,SUM(LEN(C738)-LEN(SUBSTITUTE(C738,{"0","1","2","3","4","5","6","7","8","9"},""))))] but it is not returning the expected result:
* String - 198503_NA_ST17 9UQ
* Expected result - 198503
* Actual result - 198503_NA
If you could give me any indication as to where I have gone wrong it would be very much appreciated.
Kind regards,
Matt
Hello!
Please try the following formula:
=LEFT(A2,MATCH(FALSE,ISNUMBER(--MID(A2,ROW($1:$94),1)),0)-1)
Hope this is what you need.
Hi Alexander,
Thanks for your help but unfortunately that is returning #N/A.
I changed the cell reference to C113 to suit where I am extracting the data from (I am extracting it into cell A113) and changed ROW references to $5:$475 as those are the rows my full data set sits in.
Have I gone wrong somewhere making those changes? I tried it without changing the ROW references but it still returns #N/A.
Thanks again for your help.
Matt
Hi,
No need to change absolute references.
=LEFT(C113,MATCH(FALSE,ISNUMBER(--MID(C113,ROW($1:$94),1)),0)-1)
If you are using Excel 2019 and below, enter this formula as an array formula. In Excel 365, you can type as usual using the Enter key.
Thanks for the site, I reference often.
Here is my new hack for this: (OFFICE365 Only)
=LET( A, MID($J2,ROW(INDIRECT("1:"&LEN($J2))),1),
F, FILTER(A, ISNUMBER(A*1)),
CONCAT(F)
)
Hello, plz
Can anybody help me out.
I have 26(4),5(7),9(10) in A1.
I want to extract the numbers like this:
26 in B1
4 in C1
5 in D1
7 in E1
9 in F1
10 in G1
Plz Help.
Hi,
We have a tool that can solve your task in a couple of clicks - Abledits Data - Split Text. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.