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 11. Total comments: 577

  1. I NEED TO EXTRACT THE FOLLOWING ITEM
    DFG554GHFGHH

  2. How would I extract these numbers without using text to column, pleas, and thank you
    Introduction to IT 4 1
    Critical Thinking and Logic 3 1
    Fundamentals of Information Security 3 1
    Introduction to Geography 3 1
    Business of IT - Applications 4 2
    Integrated Physical Sciences 3 2
    Ethics in Technology 3 2

  3. I Want to Extract number after and before with X in the below descriptions
    example 1. Huhan Series XL38SX4 JP Limielite Product
    example 2. Shower Shampoo SM172SX1 BOX DENIM8 IN PC
    output want
    1. 38X4
    2. 172X1

    I need to extract 38X4 from the above string. I have bulk list of these type of description and the string type is not fixed. please help me to experts to extract these number in excel

  4. I have a lot of cells that looks like this:

    Omp. B. 2111215 013841 - Superbrugsens Lynge - Uggeløse fra sekr 935 52919 935 til sekr 935 52930 935

    And I need to find the number after the B., in this case 2111215. In some cells the number comes after "b" and some times after "B.". Is there any way to do this?

  5. Using the improved formula under the "How to get number from any position in a string" section, if a source string is "USEC-SATL: 100USRITM-SATL: 130", it will return "100130".
    Is there a way to adjust the formula so that it can return a comma separate value (i.e. 100,130)? I want to delimit it and get back the individual values. Thank you.

  6. Hi There,

    Please help me to get extracted the number from a string. There is a string "(CAN_39F Inc. - 35722)" and I want to get only "35722" instead of "3935722". I have used the formula given above but I get all the number written in the string. That formula is very helpful in getting number from strings like "(Meraki Group - 36785)" (extracted number "36785") but fails for strings where number is written middle of the string or start and I want only those number which are written in last after hyphen (-).

    Kindly help me to get this sorted out.

    That would be very helpful.

    1. Rahul:
      If the data is always formatted as shown in your example, the simplest way to extract the digits after the hyphen is:
      =RIGHT(A2,5) where the data is in A2. You can change the number of digits from 5 to another string length.

  7. Hello,

    I have query, I have data in cell like this
    "Owczarek TB 1, 2, Kobayashi T 2, Ramirez R 3, 4, Rong L 1, 2,3,4,5, Puzio-Kuter AM 2,". and I want to be split the data result like
    Owczarek TB 1, 2
    Kobayashi T 2
    Ramirez R 3, 4
    Rong L 1, 2,3,4,5
    Puzio-Kuter AM 2

    Please revert back me with solution.

    Regards,
    Kalyan

  8. HYE FRNDS
    I NEED A HELP
    Input erfsd9958405019e34
    Desired Output 9958405019

    1. Hello, Vishal:
      This formula will produce the desired output: =MID(A2,6,10)
      where the input is in cell A2.
      If you have the same need to extract the middle 10 digits beginning at the 6th digit then you can copy this straight down column A.

  9. HYE FRNDS
    I NEED A HELP
    INPUT LIKE THIS ( 98564, 45845, 45142, 45142)
    I WANT OUTPUT JUST SOME NUMBER LIKE (45845, 45142)

  10. Hello!
    I need to get last 6 numbers from a 10 digit number - 1000002502, but not including "0". Is there any formula with such condition?

  11. I have a question:
    I am trying to extend the autofill.

    I want it to go in sequence as A1+1, A2+1, A3+1, than A5+1, A6+1, A7+1, than A9+1 A10+1, A11+1...
    So when i drag the autofill it adds in above manner skipping after every 3 a cell

  12. Hello, Harsh:
    The data in the sample doesn't match the end result. Where is the data in the end result coming from?
    My first thought is you should try using the Text-to-Columns tool in Excel. Use the hyphen as the separator.
    If this doesn't work, try submitting your question with all the data so we can see what you're working with.

  13. Please help me for below data separation

    (Vehicle Number separation )

    - INV#7451-47KA03MF2980

    - SU081819WIN02420-OTC BILL

    (Vehicle number and Invoice number separation )

    - SU081819INS02152-KA03MZ2310-INS AMT

    Please do the needful..

  14. Please help me for below data separation

    (Vehicle Number separation )
    - INV#7451-47KA03MF2980
    - SU081819WIN02420-OTC BILL

    (Vehicle number and Invoice number separation )
    - SU081819INS02152-KA03MZ2310-INS AMT

    Please do the needful..

  15. Sudhir:
    There are a couple of techniques for extracting specific data from text strings.
    One method is to use the RIGHT, LEFT or MID functions. Another is to use Excel's Text-to-Columns tool and another is to use one of several formulae. All of the techniques are thoroughly explained and demonstrated with examples in the above article.

  16. Hi guys
    I want extract only numbers from
    e.g.
    Talk Show - (123456789)
    Pls pls share the formula.

    Thanks in Advance!

  17. how to extract the number of grams from a text like this without getting the 7x5 part
    BEAD GLASS LIGHT GOLD PIP 7X5MM PK30

    thank you

    1. Juana:
      Use the RIGHT function like this:
      =RIGHT(A2,2) where the text string is in A2.

  18. Tim:
    I can't get Excel to exhibit the behavior you're seeing, so I can't say how to correct it.
    The numbers are extracted as text. If that is a problem for you can use the VALUE function on those cells that contain the numbers stored as text. It looks like =VALUE(A2)
    Alternatively, you can use the text to columns tool in Excel, Use the fixed width option and it will separate the numbers from the text. This method produces the numbers as numbers. Even where you have large numbers of rows of data the Text-to-Columns tool gets the job done quickly.

  19. Hi guys,
    I refer to the formula to extract numbers from the beginning of the text string. i.e.,
    =LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))

    My text data looks like this:
    "3922TheftbyDeception"

    When I use this formula, I require the code '3922', but for some reason the second 2 is being eliminated, and i get an output '392'
    this is happening for all such cells where one number is repeated in the string.
    for eg., "2702AggravatedAssault" returns '270' instead of the desired '2702',
    "2902UnlawfulRestraint" returns '290' instead of the desired '2902', and so on.
    Please help, I'm at my wits end.
    thank you
    Tim

  20. I have a string of numbers 20180818
    I need to extract month and year in MMMM/YYYY format

  21. Hello. What if I have a specific number, for example 1231, but that number is obtained by a sum of 3 numbers? Can I extract numbers from that sum formula?

  22. FYI, this is way more complicated than necessary. In a column to the right of the list, I pulled out one of the numbersets manually and Excel auto-filled the rest. My numbers were surrounded by text on both sides and of varying length.

  23. Christopher:
    There are two instances of "07" in this sample string.
    If you want the first instance and the next 9 digits then where the string is in C13 enter this in and empty cell: =LEFT(C13,11)
    If you want the second instance and the next 9 digits then enter this in an empty cell: =RIGHT(C13,11)

  24. Hi guys,

    I have a number string for example like "07427640900247247520603080251507427640900" and I want to extract a 11 digit number starting with 07 from within this number string. How can you do it?

  25. Hi
    I have data like:
    sodium fluoride 1.1 mg O 0.5 mg fluoride
    benzydamine
    magnesium hydroxide 3 g O
    how can extract sodium fluoride, benzydamine,magnesium hydroxide to column b
    and 1.1, 3 to column b and mg, g to column c and o in column d
    Regards
    Majid

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

  27. Hi Guys - i want to extract the 2018 from the below text string in cell A3, can someone help? TES1-TEST-Bathtimes-2018, bloggs, joe

  28. Dear,

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

    for example |1234.56 USD| to |1234.56|

    Regards,
    Ronald

  29. we have a data like this:-
    r0fsd9958405019e34 Required Data : 9958405019
    5353w9810105370qw4354 : 9810105370
    ewrew8860339000dfdf : 8860339000
    erfsd9873903709sds4
    ewrew9810241172-35
    edsd9582121827dfd35
    rdf9999377066dfs5fd
    wer9873744954df43
    53sdf9818803734adf443

    I want to extract only mobile number which is 10 digit at each place, please let me know the easiest formula to extract these mobile number in simple way.
    Regards
    Sachin

    1. Hello, Sachin,

      If we understand your task correctly, you may find our Extract Text add-in helpful. Please try to use the "Extract by position" option to extract the 10-digit mobile numbers from your cells. Just enter "6" as the position number of the first character, set "10" as the number of characters to extract and click "Insert Results".

      1. Mary, can you share the exact formula as the example of Sachin question. I also want to know how to split the number from text following Sachin's query. Thanks in advance!

  30. Can also use the simple formula as below

    =MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2))

    Then hit Ctrl+Shift+Enter

    1. Another way to work around it.

      =RIGHT(A2,LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))+1)

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

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

    Any help much appreciated!

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

    Provsiioning bars are in N/A status. Plea

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

  34. Hi!

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

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

    15 walk
    60 gym
    10 run
    10 walk
    30 run

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

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

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

    -Stephanie

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

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

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

    1. Hi Doug,

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

      Once again Thanks.

      Regards,
      HIren

  36. Hello,

    Please help me in following.

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

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

    Please help, if anyone knows.

    Hiren
    9898132180

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

    A1 = first number48832234//second number552437548

    Result = 48832234 552437548

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

    any help is highly appreciated.

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

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

  38. Hi,

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

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

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

    Thanks,
    Andrew

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

  39. I want to extract 541 from KA54115259.

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

    2. Hi Jessica,

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

      =MID(A1,3,3)

      Where A1 is the cell containing the original string.

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

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

  41. How do I extract the $ value?
    $0.00 copayment

    1. Rosa:
      Enter this as an array formula in an empty cell and then format that cell as Number. Where the data is in C36:
      =1*MID(C36,MATCH(TRUE,ISNUMBER(1*MID(C36,ROW($1:$1000),1)),0),COUNT(1*MID(C36,ROW($1:$1000),1)))
      Remember, this is an array formula so after you enter it in the formula bar click in the formula and CTRL+Shift+Enter all in one click. Then you'll see the curly brackets around the entire formula indicating Excel knows this is an array formula.

      1. Rosa:
        Here's another formula to extract numbers from a string. It's also an array so CTL+Shift+Enter. I think this one works more reliably. So, where the data is in C37
        =LOOKUP(10^99,--MID("|"&C37,SMALL(IF(((--ISNUMBER(--MID("|"&C37, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&C37,ROW($2:$1004),1))),ROW($2:$1004)),1), ROW($1:$1003)))
        Remember to format the cell where this formula is entered.

  42. 367 cd 046 104101 Real
    131 DPU3005 104017 KRISPY
    CC MERCH ID 9189997141
    CC MERCH ID 9189997158
    POS MID 5930005 - 06/29/2
    POS MID 19520212 - 06/29/2

    i want to extract 104101, 104017, 9189997141, 9189997158, 5930005, 19520212. what formula can i use to extract this numbers.

    Thank you.

  43. Hi please help me in extracting only the numbers here:
    a.367 cd 046 104101 104101
    b.131 DPU3005 KRISPY 104017 104017
    c.CC MERCH ID 9189997141 9189997141
    d.CC MERCH ID 9189997158 9189997158
    e.POS MID 5930005 - 06/29/2 5930005
    f.POS MID 19520212 - 06/29/2 19520212

    I tried using this formula =LOOKUP(99^99,--("0"&MID(C7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C7&"0123456789")),ROW($1:$10))))
    it worked for letters c-f but for a-b it only extracted the first number 367 and 131.

    Hope you can help me with this. thank you

    1. Jam:
      Where the first data set is in A2 this should work:
      =TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,",", " "),LEN(SUBSTITUTE(A2,",", " "))-MAX(IF(MID(SUBSTITUTE(A2,",", " "),ROW($1:$99),1)=" ",ROW($1:$99)))),CHAR(160)," "))
      This is an array formula, so when you put it in the formula bar with the cursor somewhere in the formula click CTRL SHIFT ENTER. You should then see the entire formula surrounded by curly brackets.
      Copy this down the column to allow this to work on the next data sets.

      1. sir the formula did not work.
        367 cd 046 104101 Real
        131 DPU3005 104017 KRISPY
        CC MERCH ID 9189997141
        CC MERCH ID 9189997158
        POS MID 5930005 - 06/29/2
        POS MID 19520212 - 06/29/2

        i want to extract 104101, 104017, 9189997141, 9189997158, 5930005, 19520212. what formula can i use to extract this numbers.

        Thank you.

  44. HI,

    I have various lines with different patterns and wish to extract only the number. Appreciate your help and advice.

    Sa1_TC_Mthly Subs GPRS A/C 515946020_May18
    Sa1_FibreBizPac500MStatic_600912789_19Dec17-18Jan18
    Sa1_TC_Mthly Subs for A/C 515605183_14Dec17-13Jan18
    SA1_TC_Mthly Subs for A/C 601335405_Jan18

    Expected results:
    515946020
    600912789
    515605183
    601335405

    1. Newbee:
      I think there a too many numbers in the string to use a find numbers formula. After working with your sample data the easiest thing I could find was to use the Text-to-Columns tool. Once the data has been split into several cells you can copy the numbers you want and paste them where they're needed. It's nothing fancy, but it gets the job done.

  45. Thank you, Its working.

    Can you pls explain,how the function is working?

  46. How can we extract the number from this text.
    BOACK#3243004465 Mount

    1. Vikram:
      You can use the solution I provided Quinton in the reply directly above your question. It works great for your sample data. Remember after you enter the formula in the cell, put your cursor into the formula in the formula bar and click CTRL+Shift+Enter to tell Excel this is an array.

  47. Henlo, is there any formula to find numbers within a string of text, I'm wanting to make an "=if(" formula for two cells where the sum of the numbers in cell B2 are equal to the single number in B3

    1. Quinton:
      This is an array formula so you need to use the keys CTRL+Shift+Enter. In the formula bar you can move your cursor into the formula and then click CTRL+Shift+Enter.
      After you do this, there will be curly brackets around the formula which tells Excel it is an array.

      =LOOKUP(10^99,--MID("|"&D2,SMALL(IF(((--ISNUMBER(--MID("|"&D2, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&D2,ROW($2:$1004),1))),ROW($2:$1004)),1), ROW($1:$1003)))

      Enter this in an empty cell and then CTRL+Shift+Enter. You can change D2 to the address you need.

  48. Hi,

    I have this type data mainsh98kamal98545 so how to split number and text in seprate columns.

  49. Hi,
    I just count numbers mentioned in string,
    Ex: String- (123456, 124564, 123456, 321456, 526341, 253614) total numbers count is "6"
    Please help for excel.

    1. Yagya:
      You can count a specific character like a comma with this formula:
      =LEN(TRIM(D85))-LEN(SUBSTITUTE(TRIM(D85),",",""))+1
      Where the data is in D85.
      In this formula the character to count is the comma in the second spot or the first ",". If your string was separated by - then enter a - in the place of the ,.
      In your case this will return 6.

  50. Hello. May be a simple one for you guys but I would like to extract two separate numbers from a text string into separate cells. As in;

    Text string: 1 Lines for 50 mins

    Result 1: 1
    Result 2: 50

    Thanks!

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