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

557 comments

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

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

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

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

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

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

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

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

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

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

  11. hello i would like guidance on how to extract certain data from collumns
    there are just number & mixed with multiple lines

    Example :
    Cells Value
    [ B2 ] 100
    [ B3 ] Phone：3050
    Cards：91
    Banks1：33.52
    [ B4 ] 500
    [ B5 ] 600

    I would like to get the number extracted on the next row so it only show number :
    Cells Value
    [ B2 ] 100
    [ B3 ] 3050
    [ B4 ] 500
    [ B5 ] 600

  12. Hi,

    How can I extract the number that does not contain any text?

    Eg:
    P2_G1038_E_29.docx
    It will extract 29

    P2_G2151_H_05.docx
    It will extract 05

    P2_R7078_H_03_xxxx_xxx_xxxxxxxxx_control_after_printing.pptx
    It will extract 03

    P2_TW3429_H_Z8F78061234.pptx
    It will extract nothing

    Thanks & regards,
    Nurul

  13. Hi i need to extract 4700090584/4700083771 number from below text

    BH-24&4700090584ARC FOR MONSOON PREPARATION & OTHE
    4700083771/JAMADOBA COLLIERY/RC96

    What will be the formula?

    Thanks & Regards

  14. Dear Sir,

    From this text UTR,BKIDY23129107772,BANK OF INDIA, I want "BKIDY23129107772" this Alphanumerical separate in next column.

    Thanks & Regards
    Pawan

  15. Thank you very much for the formulas, i need to extract exact numbers from string and the text contains as well other numbers. Can you please show me how to solve this?

    for example: Lnd/lnd rigts/bldgs, on 3rd-prty lnd
    12345678

    I need to get exact number "12345678"

    After applying formula: SUMPRODUCT(MID(0&C7, LARGE(INDEX(ISNUMBER(--MID(C7, ROW(INDIRECT("1:"&LEN(C7))), 1)) * ROW(INDIRECT("1:"&LEN(C7))), 0), ROW(INDIRECT("1:"&LEN(C7))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(C7)))/10)

    The result is: 312345678

    Thanks so much in advance!

    • Hi! You can extract numbers from a pattern using the custom REGEX function. For example,

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

      You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex). 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.

  16. Thank you SO MUCH for the formula for extracting numbers from a string. It works perfectly :)

  17. Thank you SO MUCH for the formula for extracting numbers from a string. It works perfectly :)

  18. Hi there! I want to extract a UPC 12 digit number from below description in excel colomn. How i do this? Your cooperation in this regard is highly appreciable. Thanks!

    "HBG86-4 Hot Wheels Make A Mactch Card Game (4 per case) UPC 887061988741 Asin B08V54CNWTt's the classic card matching that kids love made into a game with graphics themed to another childhood playtime staple – Hot Wheels cars!This game is designed"

    • Hi!
      You can extract the numeric code from the text with the custom function RegExpExtract. You can see detailed instructions and an example file in this article: How to extract substrings in Excel using regular expressions (Regex).

      =MID(RegExpExtract(A1,"UPC ([0-9]{12})"),5,12)

      You can also use Regex tools without formulas. 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.

      • Thanks! Formula working. But i face a problem because sometimes UPC have 13 digits and and when i put formula =MID(RegExpExtract(A1,"UPC ([0-9]{12})"),5,12) only 12 digits of UPC are shown. How to solve this problem so that 12 digit UPC and 13 digit UPC are shown exactly as original. Thanks Again for your cooperation.

        • Hi!
          The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. Add a choice of 12 or 13 digits to the formula. I recommend reading this guide: Excel Regex cheat sheet.

          =MID(RegExpExtract(A1,"UPC ([0-9]{12,13})"),5,20)

  19. Hi Alexander,

    I need your support on how to extract the pack-size from the text string.

    Example: XXLR SHAMP ANT-HR FALL (NPS)GF 24X200ML

    I just need 200 ML as a result. The position of the pack-size within the text string differs for every description thus can't use the formula =Left (A2, 5) .

    I would appreciate your help in this regard.

    Thanks!

  20. Hi, can you help me to extract specific number from string of numbers in a row, eg
    0 0 1 0 3 0 4 0 0 6 7 9 0 0 0 - how to extract number 4 from this string of numbers....Tq

  21. Hi, Could i extract the number of Net Outage days, hours and mins from the following text, and add them to seperate columns
    "Clock Stops - 0days 4hours 15mins Net Outage - 0days 0hours 53mins Escalated"

  22. How would I extract only the largest number from a string including double digit numbers.

    Ex (adcd)4(abcd)12(abcd)20(abcd)15

    And I need just the 20 extracted

  23. How would I get it to take all of the numbers, even if it starts with zeros? For example, I need it to pull 002, but when I use the formula above, it only pulls "2". How can I make sure that the two zeros are included? Thank you!

  24. 212LBTCE94995226 I WANT TO EXTRACT NUMBER FROM THIS TEXT

  25. Hello, how would I go about extracting only the 197, 57, and 166 (the dimensions) from the following cell? Ideally each extracted number should be in a separate column.

    Package 1 — L: 197cm, W: 57cm, H: 166cm, Weight: 75kg, qty: 1

    • PS: the formula should be copied down do other cells which have different numbers

    • Hello! To extract multiple numbers from a text string, use regular expressions and the custom function RegExpExtract. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex).
      To extract not all numbers from the text, extract the desired part of the text string using this instruction: How to extract text between two characters in Excel.
      Try this formula:

      =TRANSPOSE(RegExpExtract(MID(A1, SEARCH("—", A1) + 1, SEARCH("Weight", A1) - SEARCH("—", A1) - 1),"\d+"))

      With Regex Tools, that are part of Ultimate Suite for Excel, you can find, extract, remove, or replace strings that match a regex 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.

      • Thanks for the answer. I forgot to mention that I am doing the analysis on Google Sheets (so no support for Regex). Would you have any other ideas?

  26. Hi
    How can i extract the first number (with 4 or 5 digits) from rows like these two:
    69002-ODN3-S-RFI-0197
    7082-E-RFI-0038
    I can also experience a randow row looking like this where i still want the first 4 digit number:
    #7082-E-RFI-0071

    and at the same time get no value when the rows look like these for example:
    XXX-ODN3-960
    ODN3-BIM-RFI-0014
    E&I-SWBD-RFI-057

    • Hello!
      Please check the formula below, it should work for you:

      =CONCAT(IFERROR(MID(REPLACE(A1,IFERROR(FIND("-",A1,1),1),100,""),ROW(1:10),1)*1,""))

  27. This does not work at all. I tried with their exact example 05-EC-01 and formula and it returned #N/A. So frustrated I have been wasting my with this

  28. Thank you for the information it has been very helpful to pull the numbers I need out of a text. Now i want to just delete the numbers I pulled so i can use the data for lookup.

    =LOOKUP(9.9E+307,--RIGHT(MID(A20,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A20&"1023456789")),999),ROW(INDIRECT("1:999"))))

    This worked to get what I needed.

    1ST SHIFT OUTBOUND NON CON LIFT OPERATOR PTO 8.00
    1ST SHIFT OUTBOUND NON CON LIFT OPERATOR HOLIDAY 8.00
    1ST SHIFT OUTBOUND NON CON LIFT OPERATOR REGULAR 55.75
    1ST SHIFT OPERATIONS INVENTORY CONTROL CYCLE COUNTER OVERTIME 0.50
    1ST SHIFT OPERATIONS INVENTORY CONTROL CYCLE COUNTER REGULAR 40.00
    1ST SHIFT MAINTENANCE MAINTENANCE 2 SANITATION WORKER OVERTIME 0.50
    1ST SHIFT MAINTENANCE MAINTENANCE 2 SANITATION WORKER REGULAR 40.00
    1ST SHIFT MAINTENANCE MAINTENANCE 1 MAINTENANCE MECHANIC REGULAR 64.00

    Now I want to delete the numbers at the end.

    • Hi!
      Unfortunately, your formula does not work for me. To remove numbers from text try this formula -

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

      • That works to remove all the numbers but I only wanted to remove the numbers on the right.

        • Hi!
          To remove the last word from a text string, use this formula:

          =TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",20)), LEN(SUBSTITUTE(A1," ", REPT(" ",20)))-19,20,""))

          • Thank you that worked perfect. I really do appreciate your help.

  29. Hi,

    How to extract mobile number alone from the below text string

    "ABC Imaging & Diagnostics,
    80-5/4-3, TH Road,
    Chennai-520000,
    TamilNadu, India
    E-mail: abcimaging @ yahoo.co.in
    Contact Person: ABC
    Mobile: 9009949999"

      • I've been racking my brain on how to find numbers before Certain text and this helped a lot thank you very much!!

        =MID(C3,SEARCH("GA",C3)-3,2)

        Was the Formula I used.

  30. How to put comma (,) before any number in the address formal? Numbers are not fix in the address it might be on any position.

  31. how to extract number from this string "1 PKt- Tomato 1057 - 20 K Seeds - 10850 Rs / on invoice 11000
    7 Pkt- Chilli Teja - 4 Seeds - 385 Rs / on invoice 400"

    result should be
    1 11000 10850 150 150
    7 400 385 15 105

  32. Hello, how can I pull this date from string 2/23/2078 ADM to 2/23/2078.

    PS this is bikaram shambat
    Waiting for response.

  33. Thanks it really worked.

  34. How would I extract only the largest number from a text string? Eg. if I had a string such as asd7adh8ash6 and I need just the 8 extracted.

    • Hello!
      Extract the numbers from the text as described in the article above and use the MAX function to choose the maximum.

      =MAX(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)), --MID(A1,ROW($1:$94),1)," "))

      • This doesn’t seem to work as I keep getting a #VALUE! error.

        • I now have gotten it to work. However, I sometimes have double digit numbers that I need to extract. Ex adhh6asbf17asd5
          So, I need 17 rather than 7. Thanks for your help!

          • Hi!
            I'm sorry you're not asking the right question.
            To extract the maximum two-digit number from text, try the formula:

            =MAX(IF(ISNUMBER(--MID(A1,ROW($1:$100),2)),--MID(A1,ROW($1:$100),2),""))

  35. Hello,

    How can I get the string of numbers from this text to populate with the formula but also include the dash

    Ex. 51-1780 Expenses Parent : Travel Expense

    I used the following formula and it worked but I need the dash

    • Hello!
      To extract numbers from the text along with dash, try the formula

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

      • Sorry replied to the wrong question

      • Thanks, Alexander,

        Excel shows me "You've entered too few arguments for this function"

          • Thanks for the swift answer, Alex,

            I found out the issue, it works now, but it takes all the numbers in a string and merges them into one. How can I take only the last 14 symbols (with dashes) from the right side of the cell?

            Thank you,
            Eldar

  36. Hi, the formula =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),"") does not work on numbers with decimals.

    • Hi!
      This formula cannot extract the decimal number because the decimal separator is not a digit.
      To extract a delimited number from text, try this formula:

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

  37. Billed days vs Amount of a person I have put in a concatenated formula:
    {=CONCATENATE(COUNTIF(E4:AI4,8)+(COUNTIF(E4:AI4,4)/2),"/",(IFERROR(VLOOKUP(C4,'Master'!$C$4:$P$35,14,FALSE)*(COUNTIF(E4:AI4,8)+(COUNTIF(E4:AI4,4)/2)),"")))}

    Value comes to be 21/3525
    Using your formula {=VALUE(RIGHT(D4,LEN(D4)-3))} I get the value to be 3525 !!! PERFECT

    But if there is half day of resource the above value becomes 20.5/3125, using the same {=VALUE(RIGHT(D4,LEN(D4)-3))} formula, I get a number 9852358 or so... This disturbs the entire calculation for the month!

    What formula can I use to get the right cost if a person takes half day off (so 4 hours instead of 8) so the monthly cost comes right?

      • @Alexander Trifuntov - The solution worked !!!
        THANK YOU

  38. Hiya,

    Do you know how I can extract the first number in a string? Example below

    Cell value = dasd adas adsad 001234 dasd adas adsa 06253
    Expected result = 001234

    Thanks,

    Kim

  39. Hi, I used your formula for my data, and it works!
    But the problem is the number extracted is only the number, how if I need to extract number with point?

    For example:
    "afhb jafbjdk 2.60sad aksd" to be "2.60"

    Thank you

    • Hello!
      To extract all numbers and dots from text try this formula -

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

      I hope it’ll be helpful.

  40. i have lot of different word, like these 08048K0922, 45002CVC922, 9.5050K0722, 10.505CVC0722 i want to separate text specific value from that words like 08K,45CVC or 45CV, 9.5K, 10.5CV or CVC. how can i doing this?

  41. how to separate only numbers from mixed letters from a cell in excel.
    NEFT/TB/AXTB222710184240/AMARF

  42. This is sheet Winter ParkDownload cell B2

    Private Dbl Queen Room Deposit (Amount: 750.00 US
    Arrive on Saturday (Amount: 240.00 USD)
    Sunday Lift Ticket (Amount: 160.00 USD)
    Service Fee (Amount: 15.00 USD)
    Total: 1,165.00 USD

    Is there a formula to take the Total 1,16500 in cell B2 to another tab on the same sheet called WinterPark, Cell M8. I only want to have the Total $ amount to the cell not the text "total" Any help would be greatly appreciated. Thank you! Chuck

  43. HOW CAN WE EXRACT THE SPECFIC NUMERIC AND TEXT FROM THIS STRING "EXIT 2000x500 KB"
    OUTPUT SHOULD "2000x500".. PLEASE SOMEONE SHOULD EXPLAIN THIS QUERY.

  44. I need to extract a number from a string of text and the number is sometimes negative and sometimes positive. Can these formulas be modified to capture the negative sign with the number? Thank you! p.s. I love the ablebits Excel add-in!

    • Hello!
      To extract a negative number from text, try this formula:

      =--(IF(MID(A1;MIN(FIND({0\1\2\3\4\5\6\7\8\9};A1&"_0123456789"))-1;1)="-"; MID(A1;MIN(FIND({0\1\2\3\4\5\6\7\8\9};A1&"_0123456789"))-1;1);"") & SUMPRODUCT(MID(0&A1; LARGE(INDEX(ISNUMBER(--MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1))*ROW(INDIRECT("1:"&LEN(A1)));0); ROW(INDIRECT("1:"&LEN(A1))))+1;1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10))

      I hope it’ll be helpful.

  45. I have a cell valeu as 1 hr 30 min or 4 hours and other as 4 hrs 20 mins, I need to extracrt hr value in one coloumn and min vaue in other colomn, to calculate the total time elapsed, can you please suggest a way to do so, because data is filled by someone so using =LEFT(I3,FIND("hr",I3)-1) value does not work always as "hr" could be written as "Hr", "Hrs" or "hours". Need your help on this one.

    For Example
    1 hr 30 min should come out as 1.5 hrs
    24 hrs should come out 24 hrs
    17 hrs 20 mins should come out as 17.3 hrs

  46. How can I use the string of number position to return #name#
    e.g TF220721054112, if the 8th position read as '1' then the result to returned as #IMPORT#.
    If the 8th position read as '4' then the value returned as #Structure#

    Appreciate your advice.

  47. Need Result Like
    one cell value 9871541 and second cell only 9852 so i want match second cell value to first
    if second cell starting digit match with first cell starting digit then give value or highlight that cell

  48. Hi, How to extract number from combination of 4".
    Thanks
    Sandeep

    • Hi!
      Have you tried the ways described in this blog post? Pay attention to the second paragraph: How to extract number from the beginning of text string.

  49. Hi I need to extract Roman number from below text

    School Package + Live Classes Yearly,IX(1) | School Package + Live Classes Yearly,X(1) | School Package + Live Classes Yearly,VII(1) | School Package + Live Classes Yearly,VIII(1)

    Result:- IX,X,VII,VIII

  50. Hi i need to extract number from below text
    -p00881-shahwar taskeen-2-d-5/4 nazimabad no 2 -0322-2929699 : 021-36684652-1-blvrd crnr-precinct-38-street 04 & link road 04

    Number i wanna generate the number is 0322-2929699

    What will be the formula

    • Hi!
      Your data does not contain any template by which you can determine the desired section of text. If such a pattern exists, please describe it.
      To extract part of the text, you can use the MID function.

      =MID(A1,49,13)

      But I don't think that this will work for all data.

      • Can u guide me how to sort this data

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