Comments on: How to extract number from string in Excel

To extract number from string in Excel, it'd take 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. Continue reading →

Comments page 2. Total comments: 389

  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!

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

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

      1. Thank you! Now what if there were multiple products in the same string, so the "product unit price" appeared multiple times in the same string?

  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

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

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

      1. 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 to give a separator in the "How to get number from any position in a string" with advanced formula

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

        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)," ")))," ","_")

          1. This works

            1. Hi Alexander how can we Sum the numbers?

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

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

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

      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.

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

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

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

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

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

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

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

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

  13. 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\"}"}

      1. 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\"}"}

          1. Dear sir,
            Below mention the data to extract following data LFT23207NST5JWP3

            Auth-26/07/2023 TRANSFER LFT23207NST5JWP3 SHAHBAZ NAZIRA

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

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

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

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

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

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

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

    1. Hello! To extract the first number from the text, you can use the user-defined function RegExpExtract. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex).
      For example:

      =RegExpExtract(A1, "\d+", 1)

      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.

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

    1. Hello! Use regular expressions and the custom function RegExpExtract to search for pattern characters in your text. For detailed instructions and examples, see here: Regex to extract strings in Excel (one or all matches).

      =MID(RegExpExtract(A1, "_([0-9]{1,3})"),2,10)

      To avoid installing VBA code in your workbook, you can use the Regex Tools. 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.

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

  19. Dear Sir,

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

    Thanks & Regards
    Pawan

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

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

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

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

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

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

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

      1. Thanq Very Much.

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

      1. Thank you. Ill give it a go

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

    1. Hello!
      You can extract substrings matching a given pattern using regular expressions. Use custom function RegExpExtract, as described in this guide: How to extract substrings in Excel using regular expressions (Regex).
      You can extract the maximum number from the text using the following formula:

      =MAX(--RegExpExtract(A1,"\d+"))

      With Regex Tools, 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      1. Thanks, Alexander,

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

        1. Hi!
          The formula works. What version of Excel are you using and what are the separators in the formula?

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

      2. Sorry replied to the wrong question

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

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

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

      1. @Alexander Trifuntov - The solution worked !!!
        THANK YOU

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

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

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

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

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

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

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

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

    1. Thank you so much. Appreciate your help.

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

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

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

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

      1. Can u guide me how to sort this data

  45. Hi there. I appreciate the detailed approach to this challenge. I have the following text:

    "[SW] 183 Patterson -- WAN1 DOWN -- PL"

    I'm using the formula above "How to get number from any position in a string"

    Instead of returning 183, it returns 1831 - adding an extra 1 at the end?

    Another text:

    [SW] 016 Bremerton -- Router DOWN -- PWR" returns 16 instead of 016?

    All thoughts are very much appreciated.

    1. Hello!
      Your first example has 4 digits. All of them are returned by the formula. In the second example, the result is returned as a number, not as text. Excel removes leading zeros from numbers.
      To extract numbers as text, try this formula:

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

      You can also extract numbers from text using regular expressions (Regex). I recommend this tutorial with examples: Regex to extract number from string.

  46. Hi,

    Thanks for sharing this very helpful code. I have a problem with number that has digit like 1.5 or 3.70 it turn to 15 and 370. Could you please guide me how to fix that :)

    Thanks again!

    1. Hello!
      To extract a number with a decimal separator from text, use the formula:

      =CONCAT(IFERROR(MID(A2,(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))+ (MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1)="."))* ROW(INDIRECT("$1:$"&LEN(A2))),1),""))

      I hope I answered your question.

  47. This formula looks so close to what I need. I'm wondering if someone wouldn't mind showing me how to tweak it to suit my requirement?

    We receive many payments each day with various narratives, I would like to automatically identify references which are 7 digit number beginning with "13" or "14". Examples below and to the right is the currently formula result.

    I am using this formula, essentially copied from above comments and info - I think I need to have the result only report back when the sequence beginning with "13" or "14" is 7 digits in length.

    Any thoughts?

    =IF(SUM(LEN(A22)-LEN(SUBSTITUTE(A22, {"13","14"}, "")))>0, SUMPRODUCT(MID(0&A22, LARGE(INDEX(ISNUMBER(--MID(A22,ROW(INDIRECT("$1:$"&LEN(A22))),1))* ROW(INDIRECT("$1:$"&LEN(A22))),0), ROW(INDIRECT("$1:$"&LEN(A22))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A22)))/10),"")

    PAYMENT FROM MR SIMON ZHAO LPSPR 8486 No result-correct
    TRANSFER 1322500 midas prch FROM SOON LEGAL 1322500-Correct
    TRANSFER Ref 1401272 Susann FROM J BOGAN 1401272-Correct
    PAYMENT FROM MS NILU JONCHHE Lot 136/Carseldine + Lot136/Carseldine 136136 - Incorrect
    0491400550 HORNSBY 491400550-Incorrect
    TRANSFER 1398031 FROM SETTLEMENT FUNDS 1398031-Correct

    Thank you

    1. Hi!
      To extract 7 digit number beginning with “13” or “14” from text, you can use the formula

      =(MID(A2,SEARCH(" 13",A2)+1,7)* ISNUMBER(--MID(A2,SEARCH(" 13",A2)+1,7)))* ((--MID(A2,SEARCH(" 13",A2)+1,7)* ISNUMBER(--MID(A2,SEARCH(" 13",A2)+1,7))) > = 1000000)*(MID(A2,SEARCH(" 13",A2)+8,1)=" ")

      For the number "14", replace "13" with "14" in the formula.
      You can also use regular expressions to extract numbers from text.
      At the link above you will find detailed explanations and examples of how to extract text and numbers from a string. Here is an example formula that will solve your problem:

      =RegExpExtract(A1&" "; "\s[1][34]\d{5}\s")

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  48. Hi, i need to extract a group of text from a cell, and sum them together. before i begin, i would like to clrify that, I am not an Excel pro user like accountant. I'm using it as a data link between AUTOCAD and EXCEL. so, please bear with me.

    A B c
    1 YES 1800/ 900/ 1200 X 500 3900
    2 NO 600/ 100/ 300/ 20 X 15 0

    YES and NO is in column A (row 1 and 2 respectively). while the number is in column B. and the sum will appear in column C next to each row. i'm looking for a formula to extract 1800, 900, and 1200 to sum it up into 3900 (1800+900+1200), and it only extract and sum it when the column A says "YES", and will not do the sum when A says "NO".

    1. sorry, the sample text arrangement went wrong.

      ignore the A,B,C above the sample and 1,2 on the left

      1. Hi!
        You can extract numbers from text using substring functions with these formulas:

        =--LEFT(B1,SEARCH("/",B1)-1)
        =--MID(B1,SEARCH("/",B1)+1,SEARCH("/",B1,SEARCH("/",B1)+1)-1-SEARCH("/",B1))
        =--MID(B1,SEARCH("/",B1,SEARCH("/",B1)+1)+1,SEARCH("X",B1)-1-SEARCH("/",B1,SEARCH("/",B1)+1))

        Write the condition using the IF function:

        =IF(A1="YES",=--LEFT(B1,SEARCH("/",B1)-1),"")

        General formula for three numbers with condition:

        =IF(A1="YES",LEFT(B1,SEARCH("/",B1)-1)+ MID(B1,SEARCH("/",B1)+1, SEARCH("/",B1,SEARCH("/",B1)+1)-1-SEARCH("/",B1))+ MID(B1,SEARCH("/",B1,SEARCH("/",B1)+1)+1, SEARCH("X",B1)-1- SEARCH("/",B1,SEARCH("/",B1)+1)),"")

        1. i see. Thanks for your help. i will need some times to explore what you shared with me. looking at the formula you gave, is killing my brain. lol. will get back to you how it goes.

          Thanks again for sharing

    2. sorry, the sample text arrangement went wrong.

      YES 1800/ 900/ 1200 X 500 = 3900
      NO 600/ 100/ 300/ 20 X 15 = 0

  49. Hey i have some problem.

    My job is to extract number from a cell. A cell has 4 digits of randomly generated number (ABCD). So i want to automatically extract number A into one cell, B into one cell and the rest. and sometimes i need to extract 2 number from the same cell. is there a clean way to do it?

    1. Hi!
      To extract the first digit from a number, use the LEFT function.
      LEFT(A1,1)
      To extract the second digit use the MID function.
      MID(A1,2,1)
      Also, use MID to extract the third and all other digits.
      MID(A1,3,20)
      Look for the example formulas here: Excel substring functions to extract text from cell.
      These functions extract a digits as text. To convert it to a number, use these guidelines: Turn text into number with mathematic operations.
      I hope it’ll be helpful.

  50. Hi I hope you could help me!
    I have some prices i need to extract...

    ?XIAOMI?
    ?Redmi 9A 32GB/2R 399.900 A,G??
    ?Redmi 10 64GB 744.900 B??
    ?Redmi 10 128GB 839.900 G??
    ?Poco X3 128GB/8R 949.900 G,A ??
    ?Poco X3 Pro 128GB/6R 909.900 N??

    My question is... Is there a clean and simple way to only obtain the prices from this cellphones For example telling a formula to look for more than 4 consecutive numbers and extract them.

    Look at this...

    Redmi 9A 32GB/2R 399.900 A,G??

    When I use a formula to look for only numbers it extracts 9322399900
    I need something capable of skiping small number secuences and search for numbers bigger than (9A) (32GB) (2R) in this case 399000
    I know that I can search for common characters like ($) $399000 to use text to columns.
    But I have a wide range of providers that dont use any particular sign for me to use.

    1. Hello Simon,

      The only solution I can think of is using regular expressions. For this, you will need to add a custom RegExpExtract function to your workbook - the code and the detailed instructions are on the above-linked page.

      As for the regex pattern, I don't think searching for 4 consecutive numbers will work in your case, because the prices in your sample strings have a thousands separator (period). So, you can use the below regex to match a substring consisting of 1-3 digits, followed by a period (.), followed by 3 digits.

      Pattern: \b\d{1,3}\.\d{3}\b

      The above pattern will work for numbers ranging from 1.000 to 999.999. If your real dataset has a wider range of numbers, you'll have to adjust the pattern.

      1. Thanks a lot I have never heard of that so I will study about regex and tell you how it goes for me.

        1. It worked perfectly. Could you please help me adjust the pattern for millions. I tried to interpret your formula but i couldn´t understand which is the key to increment to millions
          I got some examples

          Store A Example
          Poco X3 Pro 8/256 GB $1’050.000 N-A ??
          Poco X3 GT 8/128 GB $1’230.000 ??
          Poco X3 GT 8/256gb $1.340.000 ??Blanco

          Store B Example
          IPHONE 12 MINI 64GB
          BLANCO
          $2.880.000

          IPHONE 12 MINI 128GB
          ROJO
          $3.100.000

          IPHONE 12 64GB
          BLANCO
          $3.300.000

          Store C Example
          11 pro max 256gb $4.200.000 negro????
          12 64gb $3.350.000 blanco verde negro????
          12 128gb $3.580.000 negro verde????
          12 pro max 256gb $5.100.000 azul????

          Store D Example
          POCO X3 GT 8/128 AZUL Y BLANCO
          $1’399.000??
          MI 10T LITE 6/128GB AZUL
          $ 1.235.000??
          MI 11 LITE 5G 6/128GB NEGRO
          $1’265.000??

          Store E Example
          Note 10 pro 128gb 6ram $1.329.000?? Azul
          Note 10 5G 128gb 4ram $949.900?? Gris
          Poco x3 pro de 128gb 6ram $ 924.900?? negro azul y bronce
          Poco x3 pro de 256GB 8ram $1.059.900?? Negro y azul
          Poco x3 GT 128gb 8ram $1.249.900?? azul negro y blanco

          There are like 15 stores sending prices over WhatsApp I would love to understand how to adjust the pattern in the most efficient way. I don´t have any problem cleaning the data before using the Regex Formula. I mean replacing... ( . , ’ ) all the characters between my prices.

      1. Thanks a lot I have never heard of that so I will study about regex and tell you how it goes for me.

        1. Thanks a lot. Both of your formulas worked great. Its amazing. I post some examples to Svetlana
          Because some of my prices are in Millions and i was not able to interpret the formula to make it take into account bigger numbers. My knowledge of excel and english is very limited but I´m thankfull of your help. I´ve been doing this process the large way for about 2 years.
          I could clean all of my data to have numbers without any signs. Example:

          iPhone 13 128GB 5000000
          iPhone 13 Pro 246GB 4500000
          iPhone 12 Pro Max 512GB 3650000

          Thanks in advance to all of you for your great work!

          1. Hi Simon,

            In case your prices have various delimiters like in the sample below, then cleaning the data before using the Regex formula is indeed the most effective way. As you said, first you remove all the characters between the prices (. , ’ ). And then, extract a number containing 4 or more digits using this simple regex:

            \d{4,}

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)