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
Hi all,
can you help me to extract the numbers from this kind of text:
RODC - Jakarta 25105372 **
RODC - Singapore 74297001 **
many thanks
Dear all,
Thank you for the post!
Can someone help me?
I want to extract value of Metre from
AXNASL137/16 JJ80029874 DOBJ JAN17 1.55M @DOLLAR1930.5 = 1.55
AXNASL14/17 JJ 80033100 DOBJ OKT17 10.6M @DOLLAR1830.0 = 10.6
YAH N133/17 JJOB F1 29.05M@DOLLAR1870 MONTREAL = 29.05
AXNASL14/17 JJOB 80033071 DOBJ OKT17 12M @DOLLAR1620.00 = 12
Thank you so much!!!
Zul:
I will provide you with a solution that will work most of the time. It will extract the Metre length from the third string in your sample, but because there is no space before the "@" it will also extract a little more. You'll have to clean this one up manually.
Where the string is in A10 the formula is:
=TRIM(MID(SUBSTITUTE(A10," ",REPT(" ",99)),MAX(1,FIND("M",SUBSTITUTE(A10," ",REPT(" ",99)))-50),99))
First enter this into an empty cell and then copy it down as far as you need. The target cell will update as you copy the formula down.
Thanks Doug....it brilliant!!! This has helped me so much, though i've to make few cleaning. You are a genius. Thank you.
I am trying to extract anything that looks like a credit card number from a string, and this could be anywhere in the cell. Assume a blank always precedes and succeeds the string.
Either xxxxxxxxxxxxxxxx or xxxx-xxxx-xxxx-xxxx.
Can you provide such a solution? I would like to know the string AND I would like to know the position in the string where it begins.
16.91cm 217415 BAKE KING ESSENCE ALMOND
I want to find the number as 217415, can help the formula in Excel
Mary:
Does this number appear in the exact same position in every line of text?
Dear all,
Thank you for the post!
Can someone help me?
I need to separate the numbers from text but in 3 dif collumns.
Examples:
Cataflam 30mg 10 cp| Col1=Cataflam / Col2= 30 / Col3= 10
Diclofenaco potassico Clavulanato 180mg 20cp | Col1=Diclofenaco potassico Clavulanato/ Col2=180 / Col3=20
Thank you very much!
Kind Regards
Aline
Aline:
The only way I could get your result was by utilizing two different multi-step processes. There may be other ways, but this is how I did it.
First, let me say thanks to Ron Coderre a MrExcel MVP who worked through this lengthy discussion back in January 2010 while working with a couple of other folks to arrive at his final formula.
Next, let me say thanks to the folks over at extendoffice where I found this handy piece of VBA code. Here is the address: https://www.extendoffice.com/documents/excel/1625-excel-extract-text-from-alphanumeric-string.html#a1
If you cannot save your workbook as a macro-enabled workbook, you will need to employ a different multi-step process that is not too hard.
To begin here is the data I started with: Diclofenaco potassico Clavulanato 180mg 20cp.
Notice there are spaces between the chemical names and the drug amounts, but not within the drug amounts. Keep your data structure consistently like this and you will be able to use either of these processes. If you do not, the methods will not work.
OK, the multi-step process not using the VBA.
The first part of this process is putting all the data into separate cells.
First, select the data. Second, in the Data tab select Text-to-Columns. Third, in the Text-to-Column window select the delimited button, then next. Fourth, select the spaces button then next. Fifth, select the general button then finish. Now your data is in five separate columns.
The second step is to combine the chemical names into one cell. For the sake of discussion, let us say the data is now in cells A2 to E2. First, select an empty cell where you would like to see the drug name, let us say B3. To accomplish this you will concatenate the names with =Concatenate(A2,” “,B2,” “,C2). Now the drug name is in one cell.
The fourth step requires the use of an array. This means after you enter the complete formula in the formula bar, select the entire formula and select CTRL+SHIFT+ENTER or CSE as the boys in the backroom say. This will let Excel know this is an array and Excel will enclose the formula in curly brackets. Entering curly brackets via the keyboard will not work. So, select the entire formula then CSE.
Keeping the array rule in mind you can extract the numbers. After the text-to-columns step 180mg is in cell D2. So, let us say you would like to see the 180 in C3. Enter this formula in C3:
{=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)))}
After the text-to-columns step 20cp is in cell E2 and keeping the array rule and the CSE part in mind you move to the fifth and final step in this process where you extract 20 to cell D3. To accomplish this enter this formula in D3:
{=LOOKUP(10^99,--MID("|"&E2,SMALL(IF(((--ISNUMBER(--MID("|"&E2, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&E2,ROW($2:$1004),1))),ROW($2:$1004)),2), ROW($1:$1003)))}
It is the long way round the barn, but I think you have achieved the result you wanted.
Hi - How do get the number with decimal
Examples : 3.5 days , 4.5 days?, 1 day?, 1 day
I need 3.5,4.5,1,1 pulled out respectively from each text
Thanks in advance
Suresh:
If you have 3.5,4.5,1,1 as text in one cell and you want to separate the numbers into four separate cells select the one cell containing the numbers and then select the data tab then text to columns then choose the delimited option then next then the comma button then finish.
This puts the numbers in separate columns. If you'd rather have the numbers in one column in separate rows then select the numbers in each column, copy them then highlight a cell in one row then from the paste tab choose paste special then the transpose option and the numbers will be pasted into separate cells in one row.
Hi, want to extract the right side of equation.
PARK HILL 3277 = 3277
CONWAY,AR 10667 = 10667
TRADTNL 16849 3a = 16849
ToC 011066 = 011066
Thanks!
Randy:
I think the simplest method is to use Excel's built-in Text to Column tool.
Highlight the data then
Under Data choose the Text to Column tool.
In the Text to Column window choose Delimited then Next
Then select the Other radio button and enter "=" in that field then next
Then if you want to format the numbers as something other than General you can do it here
Then select Finish and you're done.
Hi here is the text "Brd 2Duplex&1VDM Btm 21.5x95.5" , want to extract 21.5 in one cell and 95.5 in another cell , it is not supposed that all description comes with sizes at the end sometimes text come at the end. thanking you.
Can anyone help me to extract and match few alphanumeric consecutive records in an excel
Excel Records - Pattern is like
1) _00004Y74A 4427
2) _00004Y74A 4428
3) _00004Z74A 4429
I have to pick numbers from the right of the string till user get some character /special character.
e.g, 1st record - we have to pick 4427 and ignore _00004Y74A
Then I will go to the next row and find out again numbers from the right of the string till user get some character /special character .
If number is consecutive (4428 in this case) , then I have to match the string before the consecutive numbers e,g,'_00004Y74A' is same for first and second record and next four numbers are consecutive.
As we can see third record doesn’t have same string before the last four consecutive numbers. So this will not be picked
So result will for alphanumeric consecutive invoices will be
1) _00004Y74A4427
2) _00004Y74A4428
This has helped me so much. You are a genius. Thank you.
Could one of you Excel masterminds help with this simple formula?
Needs the first digit of the group of numbers, but if it's only 3 numbers needs to = 0.
e.g.
i3-520 need = 1
i5-550 need = 1
i5-650m need = 1
i5-2500k need = 2
i5-3500L need = 3
i7-6700HQ need = 6
i5-7700HK need = 7
Something along the lines of locate the group of 3-4 numbers, then output the first digit unless it's less than or equal to 999 in which case output
1.
simple as =LEFT(RIGHT(A1,3),1) if data is in A1
Guys,
How to find 2nd highest number from one string cell by cell.
2543
result should be 4
543257
result should be 5
Any solution?
Hi,
i am successful in using this formulas for my work but i want to use this same formula in VBA. when i tried it in VBA for copy pasting this formula for multiple cells, it throws error. can you please help me on this.
Zulfi
hi guys!
I have a credit card number starting with 5433xxxxxxxx3019. how can I find the masked numbers through excel? or do i have to enter any other formulae?
If the credit card number is in Cell number A1, then =MID(A1,5,8) formula will extract masked xxxxxxxx number.
Thanks vipul....it worked!!! sorry for my late reply...
Try this and put vlookup formula instead of A2 if you have database with full credit card no. or I need one example of .xlxs file so that I can give you precise support.
=IF(LEFT(A2,4)="5433",A2,"")
Dear Ladies,
Could you please help with the formulas?
L250x250x25
I need to extract the profile and thickness in separate cells.
L250x25
Thank you in advance!
Try this
=LEFT(A2,5) & RIGHT(A2,2)
hope this helps...
Dears ,
Could you please help with the formulas?
in cell A1 i input Forse41.24 and in cell B1 i input Forse 41.24
I need to extract the number and text in separate cells.
41.24 and Forse
Thank you in advance!
Forse
Hello,
If I understand your task correctly, please try the following formula:
=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{".","0","1","2","3","4","5","6","7","8","9"},""))))
=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{".","0","1","2","3","4","5","6","7","8","9"},""))))
Hope it will help you.
Dear Svetlana!
Thank you so much!!!
Best regards,
Vitaly
Dear Ladies,
Could you please help with the formulas?
The input is LME_04329_100_VSH_04122017_SHUBIN_AMEND
I need to extract the date and surname in separate cells.
04122017 and SHUBIN
Thank you in advance!
Vitaly
Hi Vitaly,
With input in A2, the formulas go as follows:
To extract the date:
=MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),4))+1, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),5)) - FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),4))-1)
To extract the last name:
=MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),5))+1, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),6)) - FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),5))-1)
Please note, the formulas work only for strings of the same pattern where a date is always between the 4th and 5th underscores and a surname is between the 5th and 6th underscores.
Extract date
=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",100)),400,100))
Extract SHUBIN
=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",100)),500,100))
Hello, Ramkey,
The formula below should help you:
=SUMPRODUCT(MID(0&RIGHT(A1,LEN(A1)-FIND(")",A1)), LARGE(INDEX(ISNUMBER(--MID(RIGHT(A1,LEN(A1)-FIND(")",A1)), ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1))))), 1))*ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1))))), 0), ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1))))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1)))))/10)
If you need a quicker solution, then you can use our add-in. The last point of the article above explains how it works.
Hope this helps!
eg: 1 (12) 500 555-0117 shall change to 5005550117
Write a formula to extract the numbers, eliminating all the spaces symbols state codes
Input result
1 (21) 500 555-0145----> 5005550117
Write a formula to extract the numbers, eliminating all the spaces symbols state codes
for eg eg: 1 (12) 500 555-0117 shall change to 5005550117
thanks for advance
regards
RAMKEY
my requiremnt is