How to extract number from string in Excel

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.

RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

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:
alt=

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, "")
An improved formula to extract number from the end of a string

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:

LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

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:
Formula to extract number from the beginning of text string

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 :)
Formula to get number from any position in a string

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):
An improved formula to extract number from anywhere in a string

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:

  1. Go to the Ablebits Data tab > Text group, and click Extract:
    Extract tool for Excel
  2. Select all cells with the source strings.
  3. On the Extract tool's pane, select the Extract numbers radio button.
  4. 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.

  5. Click the Insert Results button. Done!

Extract numbers and insert the results as formulas or values.

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:
Numbers are extracted from strings as values.

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)

Table of contents

565 comments

  1. 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?

  2. 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.

  3. 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)

  4. 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?

  5. 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

  6. Dear,
    a1 a2
    I have BXC123644 i want rezultate BXC1 all text and one number
    AEB56984fg5 AEB5
    CT12564984 CT1
    CDTMS56DGT CDTMS5

  7. 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

  8. Dear,

    do appreciate your guidance how to extract number with decimal from excel?

    for example |1234.56 USD| to |1234.56|

    Regards,
    Ronald

  9. 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!

  10. 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)

  11. It's great! but shamelessly the formulas in english doesn't work in german... So, thanks anyway! :D

  12. Please Help!
    I have a text string containing comma delimited numbers in a single cell, e.g. "2, 3, 4, 10, 11". I am looking for a formula that looks for a given number and returns TRUE if it is contained in the string or FALSE if it is not. The formula must not confuse numbers with characters, e.g. looking for "1" in the example string above must return FALSE.

    Any help much appreciated!

  13. Type of Issue: Booking failure (order in entered status)
    Here as detailed description of issue as possible.: Order 787895 is stucked can you book that. Thank you!
    Order number: 787895
    Priority: High

    Provsiioning bars are in N/A status. Plea

    ----- Please help me to write excel formulate to get order numbers

  14. Hi!

    I am having trouble with my formula, despite following the instructions step by step!

    I want to add numbers associated with different words. Example:

    15 walk
    60 gym
    10 run
    10 walk
    30 run

    I want the sum... Walk= 25 Gym=60 Run=40

    To rephrase, I want the sum of all digits associated with "walk" OR "gym" OR "run".

    I can't figure this one out! Thank you for any help you're able to provide!

    -Stephanie

    • Stephanie:
      Enter "Run", "Walk" and "Gym" in cells C62, D62 and E62 respectively. These will be the headers.
      Enter the data in A48:A57. The formula is case sensitive so be sure the data matches the caps in the headers.
      In C63 enter =SUM(IF(ISNUMBER(FIND(C62,$A$48:$A$57)),VALUE(LEFT($A$48:$A$57,FIND(C62,$A$48:$A$57)-1)),0))
      then with the cursor in the formula bar in the formula click the CTRL Shift Enter keys at the same time. This is an array formula so you need to tell Excel to evaluate it as an array. When you enter the formula and then in the formula bar you put the cursor in the formula and click the CTL SHIFT ENTER keys it will put curly brackets around the formula which indicates to Excel that this is an array.
      When the value appears in E63 copy the formula over to D63 and E63.
      As you enter more data in the A range be sure to change the second cell address to match the last cell in the range. Right now the range is A48 to A57. If you add more data change the A57 to another cell address. Remember, there are three places in the formula for that range.

      • Stephanie:
        I should have written:
        "When the value appears in C63 copy the formula over to D63 and E63."

  15. Hiren:
    Because each of the substrings you want to are different and the strings vary in length, I think the best you can do is to use different formulas for each.
    For example, with the string "XYZ 50MG TABLET" you want the "50MG" substring. You can use =MID(H35,SEARCH("MG",H35)-2,5). With the "PARADICLO 500/100MG TABLET" you can use =MID(I35,SEARCH("MG",I35)-7,9) to extract "500/100MG". This approach can be repeated for each string and substring. It's a little klunky, but I don't see anything other than the "MG" that's common to the strings.

    • Hi Doug,

      Thanks for your response. Yes, you are right. I had already tried this and helped me in few cases. I think, for rest of the things, I will have to do manually as the description of products are different.

      Once again Thanks.

      Regards,
      HIren

  16. Hello,

    Please help me in following.

    I have a bunch of data containing product name with its strength in "MG", Like XYZ 50MG TABLET, PARADICLO 500/100MG TABLET, ABC 100 MG CAPSULE like wise....

    I just need to extract "STRENGTH" like, 50MG, 500/100MG, 100MG, 0.375/0.5MG likewise.

    Please help, if anyone knows.

    Hiren
    9898132180

  17. Hi, I need help in extracting numbers from a string but along with spaces after each set, for ex:

    A1 = first number48832234//second number552437548

    Result = 48832234 552437548

    i am already aware of the formula to extract all numbers.

    any help is highly appreciated.

    • I am awaiting some help ... is someone there who can help ....

      • Nadeem:
        I remember this question from the other day and it isn't clear. However, if you have two numbers in one cell separated by "//" and you want to remove the "//" and substitute a space then this works:
        =SUBSTITUTE(A1, "//", " ")

  18. Hi,

    I am attempting to return only those numbers below which contain "50097" in the first part (before the hyphen) which are pre-fixed by "BA". The numbers need to be returned in the same cell.

    BA50097-52201 BA50097-63623 BA50097-64930 BA52201-50097 BA56510-50097 BA63623-50097 BA64930-50097

    I've been racking my brains. Any help would be appreciated.

    Thanks,
    Andrew

    • For clarity, I am attempting to return the second part of the string (after the hyphen) if 50097 is a match in the first part. Hope this makes sense.

  19. I want to extract 541 from KA54115259.

    • Hi Jessica,

      If you just need to extract 3 characters beginning with the 3rd one, you can use this formula:

      =MID(A1,3,3)

      Where A1 is the cell containing the original string.

    • Jessica,
      you can use MID function in excel. It returns a specific number of characters from a text string starting at the position you specify.
      in you case KA54115259 is the text string. To extract 541 from this string use =+MID(cell number,3,3). cell number in the cell where your this data appears.3 is the place of a text from where excel starts and next 3 is total number of characters you need to extract 541 i.e. 3

  20. Can anyone help with a formula for this text string? We need the most recent date from the string. Thanks you in advanced.

    Below are a few examples:
    CPG PW REV 09-01-2011~No=02-19-2010~Yes=06-01-2005~Business Closing
    CPG PW REV 09-29-2010
    CPG PW REV 05-11-2012~No=06-08-2012~New Owner~Yes=07-21-2005~Business Closing

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)