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
How do I extract the $ value?
$0.00 copayment
Rosa:
Enter this as an array formula in an empty cell and then format that cell as Number. Where the data is in C36:
=1*MID(C36,MATCH(TRUE,ISNUMBER(1*MID(C36,ROW($1:$1000),1)),0),COUNT(1*MID(C36,ROW($1:$1000),1)))
Remember, this is an array formula so after you enter it in the formula bar click in the formula and CTRL+Shift+Enter all in one click. Then you'll see the curly brackets around the entire formula indicating Excel knows this is an array formula.
Rosa:
Here's another formula to extract numbers from a string. It's also an array so CTL+Shift+Enter. I think this one works more reliably. So, where the data is in C37
=LOOKUP(10^99,--MID("|"&C37,SMALL(IF(((--ISNUMBER(--MID("|"&C37, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&C37,ROW($2:$1004),1))),ROW($2:$1004)),1), ROW($1:$1003)))
Remember to format the cell where this formula is entered.
367 cd 046 104101 Real
131 DPU3005 104017 KRISPY
CC MERCH ID 9189997141
CC MERCH ID 9189997158
POS MID 5930005 - 06/29/2
POS MID 19520212 - 06/29/2
i want to extract 104101, 104017, 9189997141, 9189997158, 5930005, 19520212. what formula can i use to extract this numbers.
Thank you.
Hi please help me in extracting only the numbers here:
a.367 cd 046 104101 104101
b.131 DPU3005 KRISPY 104017 104017
c.CC MERCH ID 9189997141 9189997141
d.CC MERCH ID 9189997158 9189997158
e.POS MID 5930005 - 06/29/2 5930005
f.POS MID 19520212 - 06/29/2 19520212
I tried using this formula =LOOKUP(99^99,--("0"&MID(C7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C7&"0123456789")),ROW($1:$10))))
it worked for letters c-f but for a-b it only extracted the first number 367 and 131.
Hope you can help me with this. thank you
Jam:
Where the first data set is in A2 this should work:
=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,",", " "),LEN(SUBSTITUTE(A2,",", " "))-MAX(IF(MID(SUBSTITUTE(A2,",", " "),ROW($1:$99),1)=" ",ROW($1:$99)))),CHAR(160)," "))
This is an array formula, so when you put it in the formula bar with the cursor somewhere in the formula click CTRL SHIFT ENTER. You should then see the entire formula surrounded by curly brackets.
Copy this down the column to allow this to work on the next data sets.
sir the formula did not work.
367 cd 046 104101 Real
131 DPU3005 104017 KRISPY
CC MERCH ID 9189997141
CC MERCH ID 9189997158
POS MID 5930005 - 06/29/2
POS MID 19520212 - 06/29/2
i want to extract 104101, 104017, 9189997141, 9189997158, 5930005, 19520212. what formula can i use to extract this numbers.
Thank you.
HI,
I have various lines with different patterns and wish to extract only the number. Appreciate your help and advice.
Sa1_TC_Mthly Subs GPRS A/C 515946020_May18
Sa1_FibreBizPac500MStatic_600912789_19Dec17-18Jan18
Sa1_TC_Mthly Subs for A/C 515605183_14Dec17-13Jan18
SA1_TC_Mthly Subs for A/C 601335405_Jan18
Expected results:
515946020
600912789
515605183
601335405
Newbee:
I think there a too many numbers in the string to use a find numbers formula. After working with your sample data the easiest thing I could find was to use the Text-to-Columns tool. Once the data has been split into several cells you can copy the numbers you want and paste them where they're needed. It's nothing fancy, but it gets the job done.
Thank you, Its working.
Can you pls explain,how the function is working?
How can we extract the number from this text.
BOACK#3243004465 Mount
Vikram:
You can use the solution I provided Quinton in the reply directly above your question. It works great for your sample data. Remember after you enter the formula in the cell, put your cursor into the formula in the formula bar and click CTRL+Shift+Enter to tell Excel this is an array.
Henlo, is there any formula to find numbers within a string of text, I'm wanting to make an "=if(" formula for two cells where the sum of the numbers in cell B2 are equal to the single number in B3
Quinton:
This is an array formula so you need to use the keys CTRL+Shift+Enter. In the formula bar you can move your cursor into the formula and then click CTRL+Shift+Enter.
After you do this, there will be curly brackets around the formula which tells Excel it is an array.
=LOOKUP(10^99,--MID("|"&D2,SMALL(IF(((--ISNUMBER(--MID("|"&D2, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&D2,ROW($2:$1004),1))),ROW($2:$1004)),1), ROW($1:$1003)))
Enter this in an empty cell and then CTRL+Shift+Enter. You can change D2 to the address you need.
Hi,
I have this type data mainsh98kamal98545 so how to split number and text in seprate columns.
Hi,
I just count numbers mentioned in string,
Ex: String- (123456, 124564, 123456, 321456, 526341, 253614) total numbers count is "6"
Please help for excel.
Yagya:
You can count a specific character like a comma with this formula:
=LEN(TRIM(D85))-LEN(SUBSTITUTE(TRIM(D85),",",""))+1
Where the data is in D85.
In this formula the character to count is the comma in the second spot or the first ",". If your string was separated by - then enter a - in the place of the ,.
In your case this will return 6.
Hello. May be a simple one for you guys but I would like to extract two separate numbers from a text string into separate cells. As in;
Text string: 1 Lines for 50 mins
Result 1: 1
Result 2: 50
Thanks!
Column, name, description
A - text; text with drugs and chemical names (alphanumber). One, two or more names.
B - common_name; common names of drugs and chemicals
C – synonyms; synonyms of drugs and chemicals
D – exctracted_names_origin; names from column A (drugs,chemicals). Between names the symbol ;
E – extracted_names_common
If some row contents two equal names or synonym only, one common_name is extracted in E
A______B____________C____________D___________________E
Text; common_name;synonyms;extracted_names_origin;extracted_names_common
Please copy column by column:
text
DIRECTLY COMPRESSIBLLE ACETAMINOPHEN 73% (E-Z 180)
PARACETAMOL (OR ALTERNATIVELY KNOWN AS ACETAMINOPHEN;CAFFEINE
CAPSULES --ACETAMINOPHEN-325MG; CAFFEINE 40MG
IBUPROFEN SOFTGELS, 200 MG.
SUBSTANCE (+/-)-2-(P-ISOBUTYLPHENYL)PROPIONIC ACID;N-(4-Hydroxyphenyl)acetamide etc
common_name
ACETAMINOPHEN
ACETAMINOPHEN
ACETAMINOPHEN
CAFFEINE
CAFFEINE
IBUPROFEN
IBUPROFEN
ACETYLSALICYLIC ACID
ACETYLSALICYLIC ACID
synonyms
ACETAMINOPHEN
N-(4-Hydroxyphenyl)acetamide
PARACETAMOL
CAFFEINE
1H-Purine-2,6-dione, 3,7-dihydro-1,3,7-trimethyl-
IBUPROFEN
(+/-)-2-(P-ISOBUTYLPHENYL)PROPIONIC ACID
ACETYLSALICYLIC ACID
ASPIRIN
extracted_names_origin
ACETAMINOPHEN
PARACETAMOL;ACETAMINOPHEN;CAFFEINE
ACETAMINOPHEN;CAFFEINE
IBUPROFEN
(+/-)-2-(P-ISOBUTYLPHENYL)PROPIONIC ACID;N-(4-Hydroxyphenyl)acetamide
extracted_names_common
ACETAMINOPHEN
ACETAMINOPHEN;CAFFEINE
ACETAMINOPHEN;CAFFEINE
IBUPROFEN
IBUPROFEN;ACETAMINOPHEN
Thanks!
I have following data.
cell A1 = ap232pple 2017.01.01. aaaaasb
cell A2 = gra2pe s7s 2018.01.01. ssss ed
cell A3 = ora123nge see 2018.03.05 sse
Is there a folmula to extract the date (A1 = 2017.01.01)?
Thank you very much for your help!
I got the formula for this case so far below. I don't know why it does not recognize first digit (2) so I had to manually add it..
=IFERROR("2"&MID(E5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},E5,1),LEN(E5)+1)),LOOKUP(1,0/MID(E5,ROW(INDIRECT("1:"&LEN(E5))),1),ROW(INDIRECT("1:"&LEN(E5)))) + 1 - MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},E5,1),LEN(E5)+1))),LEN(E5))
I have a column of values (see example below, column A):
What formulas do I use to split the numerical value into column B and the Unit of Measure (alpha characters) into column C, (see example, Columns B & C)??
A B C
1 1g 1 g
2 10g 10 g
3 5g 5 g
4 50mg 50 mg
5 250mg 250 mg
What formulas do I use to split the numerical value into column B and the Unit of Measure into column C, example
Formatting didn't come through correctly...
cell A1 is 1g / want 1 in cell B1 / want g in cell C1
cell A2 is 10g / want 10 in cell B2 / want g in cell C2
cell A3 is 5g / want 5 in cell B3 / want g in cell C3
cell A4 is 50mg / want 50 in cell B4 / want mg in cell C4
cell A5 is 250mg /want 250 in cell B5/want mg in cell C5
AMA:
This requires a different formula for each cell.
In the cell which will contain the number the formula is:
=LOOKUP(99^99,--("0"&MID(H40,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},H40&"0123456789")),ROW($1:$10))))
In my example the cell holding the original data is H40. You can change this cell address to whatever suits you.
The formula which will contain the units is:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H40,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
Again, my example has the data in H40 you can change that address.
Doug,
Thank you SO VERY Much!...These formulas worked!
I had racked my brain trying so many different approaches and could not find the solution.
Thank you for your help
AMA
Hello, I have quite a difficulty task, can't seem to resolve it.
Example:
Cell A1:
0123456789 Description Item 1 $150.01 0123456789 Description of item 2 $1400.01 0123456789 Description of item 3 $50.01 0123456789 Description of item 4 $15
I want to extract all the prices and sum them.
In cell B2 it should be: $1615.03 - which is sum of 150.01+1400.01+50.01+15.
Note that example in cell A1 can vary with number of items, it can go from 1 to 10 items in total, resulting in 10 prices that need to be SUMed. Also for this text string, what is constant always is the "$" before the price and space (blank) after the price.
I'm able to pull the first price only, but can't quite figure out how to move on to the next price.
Can someone help me out with this?
Djole:
You say you are able to get the first price. How are you able to do this?
Well, at that time i thought i was able with this function:
=MID(A1,FIND("$",A1),8)
But, since the price is variable, it will not work well if the price is $15 for example. I was able though to find the position of each "$" in the cell with the help of FIND and SUBSTITUTE functions.
For the example i mentioned above this will find first "$" position, then it will replace the first "$" and find the second one, etc.
=FIND("$",A2)&"+"&FIND("$",SUBSTITUTE(A2,"$","",1))
Big flaw of this method is that if you have more than 2 prices in the string, it will not work, so you'll need to add a nested SUBSTITUTE in SUBSTITUTE.
To solve this problem i had i got help from yt channel https://www.youtube.com/user/ExcelIsFun. I think they will post the solution in the next week or so.
Djole:
Because the values were not the same length, the only way I could extract the dollar values was to use Text-to-Columns using the "$" as the delimiter, then where the data had been entered into cell A8 through D8 I entered this: =LEFT(A8,FIND(" ",A8)-1)+0 in an empty cell, then formatted the cells as currency and created a cell to enter SUM(A8:D8).
Is there a way the values can be entered into separate cells as they're entered? Putting all the info in one cell makes it very difficult to work with. Try to catch it at the front end.
Could someone please help me with :
"Zadnja: *41,40*
4.6.2018
-0,48%"
I want to extract 41,40 and the cell in wich is located is B2.
Thanks
Could someone please help me with :
"Zadnja: *41,40*
4.6.2018
-0,48%"
I want to extract 41,40 and the cell in wich is located is B2.
Thanks
how to extract only numbers from a text like this "€ 1'234'567" or this "EUR 803'765" ?
ece:
This formula works great. Found it on the web some years ago.
=IF(SUM(LEN(A8)-LEN(SUBSTITUTE(A8,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&A8, LARGE(INDEX(ISNUMBER(--MID(A8, ROW(INDIRECT("1:"&LEN(A8))), 1)) * ROW(INDIRECT("1:"&LEN(A8))), 0), ROW(INDIRECT("1:"&LEN(A8))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A8)))/10),"")
Remember, there are no line breaks in it.
Thanks mate, this is awesome and I got no idea how this is working but it definitely worked...
im looking to extract a series and replace it with "-" to keep the min() and max() values of the series. Then to repeat IF() there are more series sets example:
from this:
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 506
to this:
100 TO 122 124 TO 142 506
This formula doesn't seem to work when the string of numbers I'm trying to capture starts with zeros, is there a workaround for this?
Indranovich:
Probably the easiest way to do it is to use the Text-to-Columns technique.
Highlight the data you want to split
Under the Data tab select Text-to-Columns
Click the Delimited button, click Next
Select the Space option, click next
In this window you'll see the way Excel will split the text, click Finish
The text will be in separate cells.