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

562 comments

  1. Thanks for this excellent formula! I have a curious result for longer extractions and unfortunately my data is not consistent, so I can't use any means but isolating numbers from letters/characters, for which your formula works great... except...

    My data ends up rounding at the 15th digit, The original formula you provided only pulls 14 of the actual numbers, then rounds the 15th and converts the rest to zeros.

    Example - the text I am extracting from is Donor: Para POC:Jim Smith 555-123-2120 / Ted 555-286-3092
    The result is 55512321205552900000 rounding at the 15th digit.

    I can't figure out what is making the number round. Tried fiddling with number format, looked up limitations on functions, but didn't land on a solution.

    Thank you for any help you can provide!

    • Hi! Excel shows no more than 15 digits in a number. Therefore, your number is automatically rounded. To avoid this, extract numbers from text as text. Here is an example of formulas:

      =CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$95),1)), MID(A1,ROW($1:$95),1),""))

      For Excel365:
      =LET( A, MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1),F, FILTER(A, ISNUMBER(A*1)),CONCAT(F))

      Your number will be written as text.

      • Thank you! Good to know!

  2. How would I extract the "Product Unit Price" and "Product Total Price" from the following string?:

    Product ID: 4584, Product Qty: 1, Product SKU: BAU1234567-7, Product Name: Test Game Product Used Test, Product Weight: 48.0000, Product Variation Details: , Product Unit Price: 300.00, Product Total Price: 300.00

  3. My sample data is containing decimal numbers in the end
    eg. BSDFDF SDDF2356.70
    In this case the formula is ignoring after the decimal place and the output is
    70 instead of 2356.70

    • Hi! To extract from text a decimal number that contains ".", try this formula:

      =TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=".",".","")))

      Hope this is what you need.

  4. How can i extract the two numbers in this string (text) in R3 cell:
    5 - 1000

    ?

    I tried =RIGHT(R3, LEN(R3) - MAX(IF(ISNUMBER(MID(R3, ROW(INDIRECT("1:"&LEN(R3))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(R3))), 0)))
    But it return:
    5 - 1000

    Doubt =LEFT will work...

    • Hi! I don't really understand how you want to extract and write 2 numbers into one cell. Here is the formula to extract the numbers as an array. For more information, please visit: TEXTSPLIT function in Excel: split text strings by delimiter.

      =TEXTSPLIT(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$95),1)),MID(A1,ROW($1:$95),1)," ")))," ","."),".")

      You can extract numbers from text using regular expressions. Here is a detailed instruction with examples: Regex to extract strings in Excel (one or all matches).

      =RegExpExtract(A1, "\d+")

      I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression pattern you enter. You don't need to install any VBA code.

      • Sorry might have not been clear.
        I have a cell Q3 with 5 - 1000, i want to get the 5 in numerical value (not string) in cell Q4, and I want to get numerical value of 1000 in cell Q5. Hope it is more clear that way

        Still i didn't try your answer yet, will check into it

  5. Hello
    I have a list of a lot of products 120-302-00, 126-302-05, 25-301-02, 1002-301-08,...
    I need to extract only the number in the middle (here 302 and 301).
    Can you help
    Thanks in advance

  6. How can I extract a number from inside parenthesis? I tried your formula above to extract a number from a value but there are some datasets that have a number in the name which is extracted as well and then combined with the number inside the parenthesis. I also have some data that the parenthesis in the name but those are text values. The common delimiting factor on my data is always (number).

    Example data: Joseph (Joe) Smith (4242)
    Desired output: 4242

    Thanks in advance for any help!

    • Hi! Try the formulas from that paragraph in the article above: How to get number from any position in a string. You will get the desired result.

      • Sorry it will work on this one and I tried to correct my post but had to post a 2nd one and that other post is gone now where I corrected the example data. Here is the example data I'm running into and can't figure out.

        Example data: Joseph926 (Joe) Smith (4242)
        Desired Output: 4242

        This is the one I'm running into that will just return 9264242

        Any ideas if it possible to only get a numerical value in parenthesis and ignore letters in parenthesis?

        Thank you!

        • Hi! To extract part of a text string using a pattern, use regular expressions and the custom RegExpExtract function. You can find the examples and detailed instructions here: Regex to extract strings in Excel (one or all matches).

          =-RegExpExtract(A1, "\(\d{1,10}\)")

          I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression pattern you enter. You don't need to install any VBA code. 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.

  7. How to give a separator in the "How to get number from any position in a string" with advanced formula

      • Eg:- I'm a 34 years old person whot got 20 years of experience in 1 stream line.

        If we extract number from this string via formula. O/P will be like 34201. Instead of the 34201, how we format the result as 34_20_1

        • Hi! To extract numbers from a text string with a separator between groups of numbers, try this formula:

          =SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(Q1,ROW($1:$95),1)),MID(Q1,ROW($1:$95),1)," ")))," ","_")

          • This works

            • Hi Alexander how can we Sum the numbers?

              • my Text is something like BL 5 / BR 5
                I want to extract Numbers then Sum up so the value will be 10

                but in case the text will be like BL 5 / BR 5 / TL (number here)
                output should be 11 no number indicated after the / it will treat it as 1

                let me know if not clear

              • Hi! Split the text into separate cells using the "/" delimiter. You can find detailed guidelines here: Split string by delimiter or pattern, separate text and numbers. Or use TEXTSPLIT function.
                Then extract the numbers from each cell as described in the paragraph above: How to extract number from the end of text string.
                If nothing is extracted, use the IF function to replace the result 0 with 1.
                Sum the numbers.
                I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  8. Hello, I want to extract 629 500.3- value as negative form in pharanthesis.

  9. Hello,

    First off thank you, this has saved me a lot effort.

    I am currently using the formula below:

    =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)

    But I am struggling as I require all numbers to make the correct number, i.e. when the value is x0300, I require 0300 to be the value, but instead I get 300.

    If it helps, I am on Excel 2016 so don't have TEXTJOIN or CONCAT working. Any chance there might be an answer?

    • Sorry I should have added, this is a large data set with various issues on formatting, so I can't use LEFT or RIGHT properly.

      Some examples

      x0300
      230rn9
      98.4
      784
      0342erb3

      • Hi! If you want to extract all the numbers from the text and write them as text, try this formula:

        =CONCAT(IF(ISNUMBER(--MID(A2,ROW($1:$20),1)),MID(A2,ROW($1:$20),1),""))

      • Hi!
        I'm looking for a formula than can extract a number from a cell that contains two or more numbers.
        For example:
        "I bought 4 bags@£2"
        So from the characters above that are in one cell, I want to to extract and get either 2 or 4.

  10. Could you help with a formula that would extract the minutes and seconds from a column of cells that contains this type of string;
    1 minute and 14 seconds
    4 minutes and 18 seconds
    48 seconds
    1 minute

    Thank you!

    • Hi! Your data doesn't have a common pattern. They are all different. So extract and use each number separately as described in the article above: Extract number from the right and from the left of a text string.

  11. Hi Sir. Would like to extract the numbers and values on below sample data. And would like to split them via 'space' delimiter.

    Wanderland KC - Evening 15.25 $65.62 $1,000.71

    • Sir, data per cell is not the same so no common count on characters that can be used as starting point.

      Wanderland KC - Evening 15.25 $65.62 $1,000.71
      Wanderland KC - Night 28.50 $63.21 $1,541.21
      Wanderland KC - Weekend Evening 168.75 $70.77 $1,000.40
      Wanderland KC - WKD Evening OT 56.25 $106.16 $14,005.50
      Wanderland KC - Weekend Night 13.50 $63.86 $699.71
      Wanderland KC - WKD Night OT 15.00 $95.79 $1,425.64

      These are just sample data.

  12. Excuse me sir, i need help to solve a formula for data below:

    Klapsi SuNSoa HrsaSo 023 Haiasoe saoAisS
    AkSuej Nseop 0532 JslaSk JsieA asjejr

    I need to get the first 4 word from right (Everything include number, and character), the problem is its a little random, so the word doesn't contain the exact number of character. 1 Word can be containing 1-20 character, can you please help me solve the formula for this problem sir?

  13. Excuse me sir, the formula u gave us really helpfull for me... but i have a problem, i need to extract every number from my data, from example (OPsI 002/030 IklAs) the formula u told us is working, but the only problem is the formula didn't extract the first two zero number, so the result it gave me is (2030), but the one i need is (002030), how can i the result i need?

    • Hi! I don't know what formula you used. But I assume that this formula extracts the digits from the text and returns a number. Therefore, the leading zeros are missing. To extract all numbers from text as text string, try this formula:

      =CONCAT(IF(ISNUMBER(--MID(A2,ROW($1:$95),1)), MID(A2,ROW($1:$95),1),""))

  14. How to extract the PIN CODE in the following string "DINESH AND SONS  GST 09ATFPM1158H1ZM-G T Road Jangiganj Bhadohi-15 KM FROM BHADOHI--BHADOHI-Uttar Pradesh-221310"

  15. Hi. I want to sort whole sheet based on uni ranking in australia.

    "World: 1
    Australia: 1"

    The problem is when it has few numbers such as

    "World: 10,
    Australia:100"

    "World: 15,
    Australia:150"

    How can i write the formula? Thanks!

  16. Good day :) How to give the results as below:
    Data --> Results
    3bf729 --> 3bf729
    1003121801(3BF729) --> 3bf729
    1003117898 3bf729 --> 3bf729
    1003110336 3bf729 --> 3bf729
    1003103192(3bf729) --> 3bf729
    boards 3bf729 --> 3bf729
    1003101812 3bf s/o --> 3bf
    1003097936(3bf729) --> 3bf729

  17. Doesn't work if there is a decimal point, e.g. 100.15 will only extract 15.

  18. Dear Sir,
    I need extract JA300732298 on below mention data.

    {"referrer":"https:\/\/www.joyalukkas.in\/checkout","address":"Joyalukkas India Limited","merchant_order_id":"JA300732298","item_information":"{\"EPS1128562\":\"JUL\"}"}

      • Dear Sir,
        I need extract following data to different excel columns EGPS0985876,JUL,EGPS0985893,JUL ,EGPS0985902,JUL on below mention data.

        {"referrer":"https:\/\/www.joyalukkas.in\/checkout","address":"Joyalukkas India Limited","merchant_order_id":"JA300732640","item_information":"{\"EGPS0985876\":\"JUL\",\"EGPS0985893\":\"JUL\",\"EGPS0985902\":\"JUL\"}"}

          • Dear sir,
            Below mention the data to extract following data LFT23207NST5JWP3

            Auth-26/07/2023 TRANSFER LFT23207NST5JWP3 SHAHBAZ NAZIRA

              • Dear sir,
                below mention the formula have an issue because find below,
                1,Auth-26/07/2023 TRANSFER LFT23207NST5JWP3 SHAHBAZ NAZIRA
                2,Auth-TRANSFER LFT23207Y21TVRHB FAHAD MOOSA
                so every data is different.

            • Dear sir,

              following formula not suitable because data is different every column (=CHOOSECOLS(TEXTSPLIT(A1," "),3)

              extract following data LFT23207NST5JWP3

              1,Auth-26/07/2023 TRANSFER LFT23207NST5JWP3 SHAHBAZ NAZIRA
              2,Auth-TRANSFER LFT23207Y21TVRHB FAHAD MOOSA
              so every data is different.

  19. Hi,

    How can I extract the string after the first underscore?

    Eg:
    P2_G1038_E_29.docx
    It will extract G

    P2_R7078_H_03_xxxx_xxx_xxxxxxxxx_control_after_printing.pptx
    It will extract R

    P2_TW3429D_H_Z8F78061234.pptx
    It will extract TW only and ignore D

    Thanks & regards,
    Nurul

  20. I have problem as below:
    Paid From aflb faof (259***5456)|USD 8.99|01 Jun 2023 09:22 AM|Invoice ID 401361407|Ref.BLSUB31520215260|ATEC Biodigesters International
    I just want only this number 401361407 (sometime this number have 9 or 8 character .
    could you help to provide formula.

      • it's doesn't work, I think because of my excel.
        could you provide other formula.

        • Hello! I assume that your Excel does not have these new functions. In that case, you can use the user-defined RegExpExtract function to extract numbers from text by pattern. You can find the examples and detailed instructions here: Regex to extract strings in Excel (one or all matches).
          For example:

          =MID(RegExpExtract(A1,"ID ([0-9]{5,10})"),4,20)

          I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression pattern you enter. 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.

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 :)