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 4. Total comments: 389

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

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

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

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

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

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

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

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

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

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

  11. I have following data.

    cell A1 = ap232pple 2017.01.01. aaaaasb
    cell A2 = gra2pe s7s 2018.01.01. ssss ed
    cell A3 = ora123nge see 2018.03.05 sse

    Is there a folmula to extract the date (A1 = 2017.01.01)?

    Thank you very much for your help!

    1. I got the formula for this case so far below. I don't know why it does not recognize first digit (2) so I had to manually add it..

      =IFERROR("2"&MID(E5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},E5,1),LEN(E5)+1)),LOOKUP(1,0/MID(E5,ROW(INDIRECT("1:"&LEN(E5))),1),ROW(INDIRECT("1:"&LEN(E5)))) + 1 - MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},E5,1),LEN(E5)+1))),LEN(E5))

  12. I have a column of values (see example below, column A):
    What formulas do I use to split the numerical value into column B and the Unit of Measure (alpha characters) into column C, (see example, Columns B & C)??

    A B C
    1 1g 1 g
    2 10g 10 g
    3 5g 5 g
    4 50mg 50 mg
    5 250mg 250 mg

    What formulas do I use to split the numerical value into column B and the Unit of Measure into column C, example

    1. Formatting didn't come through correctly...

      cell A1 is 1g / want 1 in cell B1 / want g in cell C1
      cell A2 is 10g / want 10 in cell B2 / want g in cell C2
      cell A3 is 5g / want 5 in cell B3 / want g in cell C3
      cell A4 is 50mg / want 50 in cell B4 / want mg in cell C4
      cell A5 is 250mg /want 250 in cell B5/want mg in cell C5

      1. AMA:
        This requires a different formula for each cell.
        In the cell which will contain the number the formula is:
        =LOOKUP(99^99,--("0"&MID(H40,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},H40&"0123456789")),ROW($1:$10))))
        In my example the cell holding the original data is H40. You can change this cell address to whatever suits you.
        The formula which will contain the units is:
        =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H40,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
        Again, my example has the data in H40 you can change that address.

        1. Doug,
          Thank you SO VERY Much!...These formulas worked!
          I had racked my brain trying so many different approaches and could not find the solution.
          Thank you for your help
          AMA

  13. Hello, I have quite a difficulty task, can't seem to resolve it.
    Example:
    Cell A1:
    0123456789 Description Item 1 $150.01 0123456789 Description of item 2 $1400.01 0123456789 Description of item 3 $50.01 0123456789 Description of item 4 $15

    I want to extract all the prices and sum them.
    In cell B2 it should be: $1615.03 - which is sum of 150.01+1400.01+50.01+15.

    Note that example in cell A1 can vary with number of items, it can go from 1 to 10 items in total, resulting in 10 prices that need to be SUMed. Also for this text string, what is constant always is the "$" before the price and space (blank) after the price.

    I'm able to pull the first price only, but can't quite figure out how to move on to the next price.

    Can someone help me out with this?

    1. Djole:
      You say you are able to get the first price. How are you able to do this?

      1. Djole:
        Because the values were not the same length, the only way I could extract the dollar values was to use Text-to-Columns using the "$" as the delimiter, then where the data had been entered into cell A8 through D8 I entered this: =LEFT(A8,FIND(" ",A8)-1)+0 in an empty cell, then formatted the cells as currency and created a cell to enter SUM(A8:D8).
        Is there a way the values can be entered into separate cells as they're entered? Putting all the info in one cell makes it very difficult to work with. Try to catch it at the front end.

      2. Well, at that time i thought i was able with this function:
        =MID(A1,FIND("$",A1),8)

        But, since the price is variable, it will not work well if the price is $15 for example. I was able though to find the position of each "$" in the cell with the help of FIND and SUBSTITUTE functions.

        For the example i mentioned above this will find first "$" position, then it will replace the first "$" and find the second one, etc.

        =FIND("$",A2)&"+"&FIND("$",SUBSTITUTE(A2,"$","",1))

        Big flaw of this method is that if you have more than 2 prices in the string, it will not work, so you'll need to add a nested SUBSTITUTE in SUBSTITUTE.

        To solve this problem i had i got help from yt channel https://www.youtube.com/user/ExcelIsFun. I think they will post the solution in the next week or so.

  14. how to extract only numbers from a text like this "€ 1'234'567" or this "EUR 803'765" ?

    1. ece:
      This formula works great. Found it on the web some years ago.
      =IF(SUM(LEN(A8)-LEN(SUBSTITUTE(A8,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&A8, LARGE(INDEX(ISNUMBER(--MID(A8, ROW(INDIRECT("1:"&LEN(A8))), 1)) * ROW(INDIRECT("1:"&LEN(A8))), 0), ROW(INDIRECT("1:"&LEN(A8))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A8)))/10),"")
      Remember, there are no line breaks in it.

      1. Thanks mate, this is awesome and I got no idea how this is working but it definitely worked...

  15. Dear all,

    Thank you for the post!
    Can someone help me?

    I want to extract value of Metre from
    AXNASL137/16 JJ80029874 DOBJ JAN17 1.55M @DOLLAR1930.5 = 1.55
    AXNASL14/17 JJ 80033100 DOBJ OKT17 10.6M @DOLLAR1830.0 = 10.6
    YAH N133/17 JJOB F1 29.05M@DOLLAR1870 MONTREAL = 29.05
    AXNASL14/17 JJOB 80033071 DOBJ OKT17 12M @DOLLAR1620.00 = 12

    Thank you so much!!!

    1. Zul:
      I will provide you with a solution that will work most of the time. It will extract the Metre length from the third string in your sample, but because there is no space before the "@" it will also extract a little more. You'll have to clean this one up manually.
      Where the string is in A10 the formula is:
      =TRIM(MID(SUBSTITUTE(A10," ",REPT(" ",99)),MAX(1,FIND("M",SUBSTITUTE(A10," ",REPT(" ",99)))-50),99))
      First enter this into an empty cell and then copy it down as far as you need. The target cell will update as you copy the formula down.

      1. Thanks Doug....it brilliant!!! This has helped me so much, though i've to make few cleaning. You are a genius. Thank you.

  16. 16.91cm 217415 BAKE KING ESSENCE ALMOND
    I want to find the number as 217415, can help the formula in Excel

    1. Mary:
      Does this number appear in the exact same position in every line of text?

  17. Hi - How do get the number with decimal

    Examples : 3.5 days , 4.5 days?, 1 day?, 1 day

    I need 3.5,4.5,1,1 pulled out respectively from each text

    Thanks in advance

    1. Suresh:
      If you have 3.5,4.5,1,1 as text in one cell and you want to separate the numbers into four separate cells select the one cell containing the numbers and then select the data tab then text to columns then choose the delimited option then next then the comma button then finish.
      This puts the numbers in separate columns. If you'd rather have the numbers in one column in separate rows then select the numbers in each column, copy them then highlight a cell in one row then from the paste tab choose paste special then the transpose option and the numbers will be pasted into separate cells in one row.

  18. Hi, want to extract the right side of equation.

    PARK HILL 3277 = 3277
    CONWAY,AR 10667 = 10667
    TRADTNL 16849 3a = 16849
    ToC 011066 = 011066

    Thanks!

    1. Randy:
      I think the simplest method is to use Excel's built-in Text to Column tool.
      Highlight the data then
      Under Data choose the Text to Column tool.
      In the Text to Column window choose Delimited then Next
      Then select the Other radio button and enter "=" in that field then next
      Then if you want to format the numbers as something other than General you can do it here
      Then select Finish and you're done.

  19. Could one of you Excel masterminds help with this simple formula?
    Needs the first digit of the group of numbers, but if it's only 3 numbers needs to = 0.

    e.g.

    i3-520 need = 1
    i5-550 need = 1
    i5-650m need = 1
    i5-2500k need = 2
    i5-3500L need = 3
    i7-6700HQ need = 6
    i5-7700HK need = 7

    Something along the lines of locate the group of 3-4 numbers, then output the first digit unless it's less than or equal to 999 in which case output
    1.

    1. simple as =LEFT(RIGHT(A1,3),1) if data is in A1

  20. hi guys!

    I have a credit card number starting with 5433xxxxxxxx3019. how can I find the masked numbers through excel? or do i have to enter any other formulae?

    1. Try this and put vlookup formula instead of A2 if you have database with full credit card no. or I need one example of .xlxs file so that I can give you precise support.
      =IF(LEFT(A2,4)="5433",A2,"")

    2. If the credit card number is in Cell number A1, then =MID(A1,5,8) formula will extract masked xxxxxxxx number.

      1. Thanks vipul....it worked!!! sorry for my late reply...

  21. Dear Ladies,

    Could you please help with the formulas?
    L250x250x25
    I need to extract the profile and thickness in separate cells.
    L250x25

    Thank you in advance!

    1. Try this
      =LEFT(A2,5) & RIGHT(A2,2)
      hope this helps...

  22. Dears ,

    Could you please help with the formulas?
    in cell A1 i input Forse41.24 and in cell B1 i input Forse 41.24
    I need to extract the number and text in separate cells.
    41.24 and Forse

    Thank you in advance!

    Forse

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{".","0","1","2","3","4","5","6","7","8","9"},""))))

      =LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{".","0","1","2","3","4","5","6","7","8","9"},""))))

      Hope it will help you.

  23. Dear Ladies,

    Could you please help with the formulas?
    The input is LME_04329_100_VSH_04122017_SHUBIN_AMEND
    I need to extract the date and surname in separate cells.
    04122017 and SHUBIN

    Thank you in advance!

    Vitaly

    1. Extract date
      =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",100)),400,100))
      Extract SHUBIN
      =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",100)),500,100))

    2. Hi Vitaly,

      With input in A2, the formulas go as follows:

      To extract the date:

      =MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),4))+1, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),5)) - FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),4))-1)

      To extract the last name:

      =MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),5))+1, FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),6)) - FIND(CHAR(1),SUBSTITUTE(A2,"_",CHAR(1),5))-1)

      Please note, the formulas work only for strings of the same pattern where a date is always between the 4th and 5th underscores and a surname is between the 5th and 6th underscores.

  24. Hello, Ramkey,

    The formula below should help you:
    =SUMPRODUCT(MID(0&RIGHT(A1,LEN(A1)-FIND(")",A1)), LARGE(INDEX(ISNUMBER(--MID(RIGHT(A1,LEN(A1)-FIND(")",A1)), ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1))))), 1))*ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1))))), 0), ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1))))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(RIGHT(A1,LEN(A1)-FIND(")",A1)))))/10)

    If you need a quicker solution, then you can use our add-in. The last point of the article above explains how it works.

    Hope this helps!

    1. eg: 1 (12) 500 555-0117 shall change to 5005550117
      Write a formula to extract the numbers, eliminating all the spaces symbols state codes

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