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)
546 comments
I have a cell with a string of ISBN numbers separated by a bar. I am trying to extract the first number that contains 13 digits and begins with 978.
0008234159|0008234167|0062678418|0062791451|006279955X|9780008234157|9780008234164|9780062678416|9780062791450|9780062799555
The above numbers are in one cell. I need the first one that starts with 978 and contains 13 digits. I can easily do a right function with there are only two and the second is the one I need. Many times this doesn't work.
How to separate the text and no. from string like this:
Where hand power only is used 97.30 166
Where any mechanically driven machinery is used 121.60 761
Fish Dehydrating - 24.30 717
Excl. erection, decoration tents and mosques 12.15 55 167
Incl. Erection 24.30 168
Floorcloth Linoleum Mfgrs. 30.15 725
Flour and Dal Mills 15.10 169
hello cen somebody please write me code for extract last 4 digits (0470) before P in serila number 1908910470P46363902R77391
Hi Aljaž,
Try this formula, where A2 is the serial number:
=MID(A2, SEARCH("p",A2) - 4, 4)
Hi Svetlana,
Thanks for the tip but when you write a formula to my table, it return error.
Any suggestions?
Ok, I found problem. , needs to be swich for ; and then it works correctly.
In a excel i have to add numbers from 2 different cells, 1st cell to have number before the decimal point and the other cell to have numbers after the decimal point.
however both cells are linked to sheet 1 from which the cell number changes upon the entry, For example
First Cell would be on D6 which is 10.25
Second cell would be on O6 which is 6.77
but the answer in cell E6 would be 10.77
Can anyone help me with this please.
hi
i have 234598 number
i want per number put on a cell
234598 2 3 4 5 9 8
I have serial number in a cell like 12345678910 and i want to some this in a different cell so how can i do this?
plse help me 8127701024 this is my whatsapp number so pls help me
i need to multiply:
40,000 sq.ft of gross building area @25.00/sq.ft
Hi I need some help!
I have cells that include an inconsistent title but all include a 6-digit meeting code. is and some of the inconsistent titles have numbers before the 6-digit code. Any thoughts?
What I have:
146761 - Trane Extended Leadership Meeting - April 2015
RFPIGR19 - 161291 - Society of Women Engineers 2019 National Conference - November 2017
RFPIGR19-161791-IWD Q1-QOR Reviews
What I need:
146761
161291
161791
DATA
(A) 10400 OFF WHITE (B) 23100 CAMEL (C) 23100 CAMEL + 10400 WHITE
NEED OUT PUT
10400,23100,23100,10400
Please help me !
Anyone please,
How can I extract the qty/value which is between texts. (e.g. 260 Individual Removal of Trees,small 303.00 each) I need to extract the number 303 only.) Thanks.
Lb29235John033921058 this is in A1
need formula for only "33921058"
need just the last set of numbers but sometimes the count can vary in length so ex...
last #s in a2 = 3 numbers
last #s in a3= 5 numbers
last #s in a4 = 9 numbers and so on
AMANACDEDITA FLOW BACK 18.85G abcde
RWK MIX DN/LAKA 4DSX12UNX135G
These are two values in cells which i need value of "G" means number which are together with "G"
Exa -1 = Input - AMANACDEDITA FLOW BACK 18.85G abcde so output would be - 18.85G because 18.85 is number with "G"
Exa -2 = Input - RWK MIX DN/LAKA 4DSX12UNX135G so output would be - 135G because 135 is number with "G"
Please Help.
thanks
Hello!
"H004.16K6170" I need to get the number out of text include 0(left and right). for example "004166170"
How can I do it? please help ;-;
Hi,
I have a 16 bit binary number in one cell, and have to read only 2 bit data from 5th position.Any excel formula for this?
For Ex : 0100100001010011 is my bin number
5th position counting from right is the 6th place(O,1,2,3,4,5)
So it is 0, taking 2 bits to right is 01
The answer is 01
I have this column, where ALL is specified in other cells eg H1=270 and H2=9300
ALL
ALL
8412
319-3234
8538-8542
356
2665
And i would like to create this:
A B
270 9300
270 9300
319 3234
8538 8542
356 356
2665 2665
Hellow
I would like to extract the number right before "days" from following an example. Could you help me?
60 years, female, white, stage:iia, alive, 588 days
female, asian, stage:iib, alive, 2759 days
80 years, female, black or african american, stage:iia, alive, 3364 days
I need
588
2759
3364
Thanks,
Very helpful solved the problem.
Hi, I have data like....agjkkhffjkkhfcnkkhfc2445543345fhhffh,
I need only numbers like 244554334 pls help with formulas.
Hi! I have "update" data in cells, like below (xxxx - string):
181215 xxxxx xxxxx xxxx xxxx 34xx 3-4xxxx 181216 2xxxx-xx xxxx xx3 xxx, etc. where 181215 and 181216 are dates.
I'd like to extract the last date (here it is 181216) from the string with functions (not with macro).
Could you help me with this? Much appreciated! Thanks!
PS: In the tutorial there's a little error at: =RIGHT(B2, LEN(A2)-B2+1). The working formula is =RIGHT(A2, LEN(A2)-B2+1) like under it.
Thank you so much for this incredibly helpful article. The formulas almostttt work for me, but I am hoping to only retrieve the last 4 characters from a column entry ONLY in the case that the last four characters are NUMERIC, and even if there are numerals/text earlier in the string. My column entries are all different lengths so I am not able to use text to columns, either. Here is an example: If my column entry were to say "Blue Sky 1st Bracket 1999", I would only like to get the "1999" in its own separate column (without the inclusion of the first '1' earlier in the string). If my second column entry were to say "Blue Sky", I would like nothing to appear in the separate column. Thank you so much for your help.
I NEED TO EXTRACT THE FOLLOWING ITEM
DFG554GHFGHH
How would I extract these numbers without using text to column, pleas, and thank you
Introduction to IT 4 1
Critical Thinking and Logic 3 1
Fundamentals of Information Security 3 1
Introduction to Geography 3 1
Business of IT - Applications 4 2
Integrated Physical Sciences 3 2
Ethics in Technology 3 2
I Want to Extract number after and before with X in the below descriptions
example 1. Huhan Series XL38SX4 JP Limielite Product
example 2. Shower Shampoo SM172SX1 BOX DENIM8 IN PC
output want
1. 38X4
2. 172X1
I need to extract 38X4 from the above string. I have bulk list of these type of description and the string type is not fixed. please help me to experts to extract these number in excel
I have a lot of cells that looks like this:
Omp. B. 2111215 013841 - Superbrugsens Lynge - Uggeløse fra sekr 935 52919 935 til sekr 935 52930 935
And I need to find the number after the B., in this case 2111215. In some cells the number comes after "b" and some times after "B.". Is there any way to do this?
Using the improved formula under the "How to get number from any position in a string" section, if a source string is "USEC-SATL: 100USRITM-SATL: 130", it will return "100130".
Is there a way to adjust the formula so that it can return a comma separate value (i.e. 100,130)? I want to delimit it and get back the individual values. Thank you.
Hi There,
Please help me to get extracted the number from a string. There is a string "(CAN_39F Inc. - 35722)" and I want to get only "35722" instead of "3935722". I have used the formula given above but I get all the number written in the string. That formula is very helpful in getting number from strings like "(Meraki Group - 36785)" (extracted number "36785") but fails for strings where number is written middle of the string or start and I want only those number which are written in last after hyphen (-).
Kindly help me to get this sorted out.
That would be very helpful.
Rahul:
If the data is always formatted as shown in your example, the simplest way to extract the digits after the hyphen is:
=RIGHT(A2,5) where the data is in A2. You can change the number of digits from 5 to another string length.
Hello,
I have query, I have data in cell like this
"Owczarek TB 1, 2, Kobayashi T 2, Ramirez R 3, 4, Rong L 1, 2,3,4,5, Puzio-Kuter AM 2,". and I want to be split the data result like
Owczarek TB 1, 2
Kobayashi T 2
Ramirez R 3, 4
Rong L 1, 2,3,4,5
Puzio-Kuter AM 2
Please revert back me with solution.
Regards,
Kalyan
HYE FRNDS
I NEED A HELP
Input erfsd9958405019e34
Desired Output 9958405019
Hello, Vishal:
This formula will produce the desired output: =MID(A2,6,10)
where the input is in cell A2.
If you have the same need to extract the middle 10 digits beginning at the 6th digit then you can copy this straight down column A.
HYE FRNDS
I NEED A HELP
INPUT LIKE THIS ( 98564, 45845, 45142, 45142)
I WANT OUTPUT JUST SOME NUMBER LIKE (45845, 45142)
Hello!
I need to get last 6 numbers from a 10 digit number - 1000002502, but not including "0". Is there any formula with such condition?
Hello, Natalia,
Please try the following formula:
=RIGHT(SUBSTITUTE(A1, 0, ""), 6)
I have a question:
I am trying to extend the autofill.
I want it to go in sequence as A1+1, A2+1, A3+1, than A5+1, A6+1, A7+1, than A9+1 A10+1, A11+1...
So when i drag the autofill it adds in above manner skipping after every 3 a cell
Hello, Harsh:
The data in the sample doesn't match the end result. Where is the data in the end result coming from?
My first thought is you should try using the Text-to-Columns tool in Excel. Use the hyphen as the separator.
If this doesn't work, try submitting your question with all the data so we can see what you're working with.
Please help me for below data separation
(Vehicle Number separation )
- INV#7451-47KA03MF2980
- SU081819WIN02420-OTC BILL
(Vehicle number and Invoice number separation )
- SU081819INS02152-KA03MZ2310-INS AMT
Please do the needful..
Please help me for below data separation
(Vehicle Number separation )
- INV#7451-47KA03MF2980
- SU081819WIN02420-OTC BILL
(Vehicle number and Invoice number separation )
- SU081819INS02152-KA03MZ2310-INS AMT
Please do the needful..
Sudhir:
There are a couple of techniques for extracting specific data from text strings.
One method is to use the RIGHT, LEFT or MID functions. Another is to use Excel's Text-to-Columns tool and another is to use one of several formulae. All of the techniques are thoroughly explained and demonstrated with examples in the above article.
Hi guys
I want extract only numbers from
e.g.
Talk Show - (123456789)
Pls pls share the formula.
Thanks in Advance!
how to extract the number of grams from a text like this without getting the 7x5 part
BEAD GLASS LIGHT GOLD PIP 7X5MM PK30
thank you
Juana:
Use the RIGHT function like this:
=RIGHT(A2,2) where the text string is in A2.
Tim:
I can't get Excel to exhibit the behavior you're seeing, so I can't say how to correct it.
The numbers are extracted as text. If that is a problem for you can use the VALUE function on those cells that contain the numbers stored as text. It looks like =VALUE(A2)
Alternatively, you can use the text to columns tool in Excel, Use the fixed width option and it will separate the numbers from the text. This method produces the numbers as numbers. Even where you have large numbers of rows of data the Text-to-Columns tool gets the job done quickly.
Hi guys,
I refer to the formula to extract numbers from the beginning of the text string. i.e.,
=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))
My text data looks like this:
"3922TheftbyDeception"
When I use this formula, I require the code '3922', but for some reason the second 2 is being eliminated, and i get an output '392'
this is happening for all such cells where one number is repeated in the string.
for eg., "2702AggravatedAssault" returns '270' instead of the desired '2702',
"2902UnlawfulRestraint" returns '290' instead of the desired '2902', and so on.
Please help, I'm at my wits end.
thank you
Tim
I have a string of numbers 20180818
I need to extract month and year in MMMM/YYYY format
Hello. What if I have a specific number, for example 1231, but that number is obtained by a sum of 3 numbers? Can I extract numbers from that sum formula?
FYI, this is way more complicated than necessary. In a column to the right of the list, I pulled out one of the numbersets manually and Excel auto-filled the rest. My numbers were surrounded by text on both sides and of varying length.
Christopher:
There are two instances of "07" in this sample string.
If you want the first instance and the next 9 digits then where the string is in C13 enter this in and empty cell: =LEFT(C13,11)
If you want the second instance and the next 9 digits then enter this in an empty cell: =RIGHT(C13,11)
Hi guys,
I have a number string for example like "07427640900247247520603080251507427640900" and I want to extract a 11 digit number starting with 07 from within this number string. How can you do it?
Hi
I have data like:
sodium fluoride 1.1 mg O 0.5 mg fluoride
benzydamine
magnesium hydroxide 3 g O
how can extract sodium fluoride, benzydamine,magnesium hydroxide to column b
and 1.1, 3 to column b and mg, g to column c and o in column d
Regards
Majid
Dear,
a1 a2
I have BXC123644 i want rezultate BXC1 all text and one number
AEB56984fg5 AEB5
CT12564984 CT1
CDTMS56DGT CDTMS5
Hi Guys - i want to extract the 2018 from the below text string in cell A3, can someone help? TES1-TEST-Bathtimes-2018, bloggs, joe
Dear,
do appreciate your guidance how to extract number with decimal from excel?
for example |1234.56 USD| to |1234.56|
Regards,
Ronald
we have a data like this:-
r0fsd9958405019e34 Required Data : 9958405019
5353w9810105370qw4354 : 9810105370
ewrew8860339000dfdf : 8860339000
erfsd9873903709sds4
ewrew9810241172-35
edsd9582121827dfd35
rdf9999377066dfs5fd
wer9873744954df43
53sdf9818803734adf443
I want to extract only mobile number which is 10 digit at each place, please let me know the easiest formula to extract these mobile number in simple way.
Regards
Sachin
Hello, Sachin,
If we understand your task correctly, you may find our Extract Text add-in helpful. Please try to use the "Extract by position" option to extract the 10-digit mobile numbers from your cells. Just enter "6" as the position number of the first character, set "10" as the number of characters to extract and click "Insert Results".
Mary, can you share the exact formula as the example of Sachin question. I also want to know how to split the number from text following Sachin's query. Thanks in advance!
Hello, Nay,
As you can see from my comment above, I recommended to use our Extract Text add-in to solve Sachin’s task.
Can also use the simple formula as below
=MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2))
Then hit Ctrl+Shift+Enter
Another way to work around it.
=RIGHT(A2,LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))+1)