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

  1. Hello Mr.Alexander Could you help me please I have example as below

    LG-101+CC100+S+22 11 21+625+3
    LG-101+CC100+S+22 11 21+625+300
    LG-101+CC109+M+22 11 21+609+220
    LG-76-2+92C+S+22 11 21+618+1140
    and I want to get only value after + at the end of the cell like below
    3
    300
    220
    1140

  2. How to match the last five numbers of the 10 figure number or string

  3. I'm trying to extract number and the text attached to it, lets say I have a list of items with different sizes as 200ml & 300ml, I want to extract 200ml 300ml from the cell, and also remove it from the source cell, is there any way with or without your tool to do this?
    Thank you very much!

    1. Hi!
      I cannot guess what is written in your data. Therefore, I can not offer a solution. Use the SEARCH function to find values.
      Perhaps something like this:

      =IF(ISNUMBER(SEARCH("200ml",A1)),"200ml","")

      You can only remove some of the text from a cell using a VBA macro.

  4. What went wrong here?

    A2: 10-Hour Orchid Class
    B2: =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&LEN(A2)+1)), 1) *1), 0) -1)

    Evaluation:
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&LEN(A2)+1)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&20+1)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&21)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:21")), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT($1:$21)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, 1, 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER("1" *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(1), 0) -1)
    =LEFT(A2, MATCH(FALSE, TRUE, 0) -1)
    =LEFT(A2,#N/A -1)
    =LEFT(A2,#N/A)
    =#N/A

    1. u can use below one

      =LEFT(A2,MATCH(FALSE,ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),0)-1)

      1. u can use below one with ctrl+shift+Enter

        =LEFT(A2,MATCH(FALSE,ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),0)-1)+0

      1. ROW(INDIRECT( "1:"&LEN(A2)+1)) seems to not be evaluating to a sequence, just the number 1 (1.00 to be precise). But I don't know why. On another machine, this formula worked.

  5. I would like to make a formula that can Extract the individual numbers from the example below. The numbers will change on either side of the x. My goal is to extract the single digits and place them in their own sell. The example below will be located in one cell group together again the digits will change depending on information.

    Example one .75 x 2 x 31
    Example two. 4 x 89 x 107

    .75 x 2 x 31 >>>> 0.75 2 31
    4 x 89 x 107 >>>> 4 89 107

    1. Replace (SpaceXSpace) with (Single ot Multiple Space/s).

  6. I Textjoin row of cells. Only one contains date and time. Other cells, if NOT blank, contains text including numbers NOT dates.

    Textjoin works but date and time is now in serial format.

    How to convert that serial format within the Textjoin Output back to date and time?

    2. I solved by extract 9 consecutive digits (including decimals) but hope better solution such as avoiding indirect...
      =
      MAX(
      IFERROR(
      IF(
      LEN(
      VALUE(
      1*
      MID(
      $A$2,
      ROW(INDIRECT("1:"&(LEN($A$2)-9))),
      9)

      )
      )=9,
      VALUE(
      1*
      MID(
      $A$2,
      ROW(INDIRECT("1:"&(LEN($A$2)-9))),
      9)

      ),
      ""),""))

  7. Hi!

    I am looking for a formula to return the first two digits of an account number

    ie account number1 =1212341234567000
    account number2 =0812341234567000

    I want to be able to return just 12 for account 1 and 08 for account 2. (I then want to assign a name for each of these first two digits).

    Please help :)

    1. =LEFT(CELLNUMBER1;2)

  8. Hello, thanks for your help! Can you please help with the following:
    Each row corresponds to one cell:

    53QBx13 bunches Limonium Piña Colada 70cm (10st) $2.60
    13QBx13 bunches Limonium Piña Colada 80cm (10st) $2.80
    8EBx10 bunches Limonium Oshi Pink BQT 60cm (20st) $3.50

    I need to extract in columns the following:
    Column 1
    53
    13
    8
    Column 2
    QB
    QB
    EB
    Column 3
    13
    13
    10
    Column 4
    Limonium Piña Colada
    Limonium Piña Colada
    Limonium Piña Colada
    Column 5
    70
    80
    60
    Column 6
    $2.60
    $2.80
    $3.50

    Is this possible?

    Thanks!

  9. 5KM (1) Back to Basics
    >21KM Sky's the Limit

    Hi, I'm amazed by Sir Alexander's superb excel skills. I'm just trying out my luck here hopefully sir can solve my problem. I would like to extract only the number '5' and '21'. I wonder it is possible. Thank you.

    1. Hello!
      Press CTRL + H. In the "Find what" field, write (*). Do not write anything in the "Replace with" field. Click "Replace".
      Then use the formula from this article and comments. For example:

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

      I hope I answered your question.

  10. Hi,

    I have a spreadsheet of thousands in the following format
    1. zvsnsnshs 2020DDE542134
    2. sgenemene2020SHB6721
    3. reenmennee 2020RTY409

    I want to extract 2020DDE542134 in 1, 2020SHB6721 in 2 and 2020RTY409 in 3

    2020 is followed by three letters but the number of digits thereafter vary.

    Please assist.

    1. Hello!
      The formula below will do the trick for you:

      =MID(A1,SEARCH("2020",A1,1),100)

      I hope it’ll be helpful.

  11. Thank you for this amazing formula!

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

    It works almost perfectly, however I would like to separate the different number sets with a space.
    e.g.
    returned value with formula: 5468751013
    desired return value: 546875 1 0 13 (number sets vary)

    Regards
    Donald

    1. Hello!
      If you want to extract groups of numbers from the text and separate them with a space, use this formula:

      =SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A2,ROW($1:$94),1)),MID(A2,ROW($1:$94),1)," ")))," "," ")

      Hope this is what you need.

      1. Wow! That worked perfectly!

        Greatly appreciate this support!

  12. BR_GID/908764_JK2

    what is the formula to get only 908764 number .

    1. Hi,
      Please check the formula below, it should work for you:

      =LEFT(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)), MID(A1,ROW($1:$94),1)," ")))," ","-"), SEARCH("-",SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)), MID(A1,ROW($1:$94),1)," ")))," ","-"),1)-1)

      I hope it’ll be helpful.

  13. Hi,

    I have a spreadsheet of a couple of thousand lot plans in the format of
    103SP122202
    10SP133260
    1RP43701
    They are always numbers followed by letters followed by numbers.
    I am looking for a formula to return all the numbers before the first letter and place in a column
    103
    10
    1
    Then a formula to return all the letters, and the numbers after the letters to place in another column
    SP122202
    SP133260
    1RP43701
    how would i achieve this?

    1. Hello!
      Write your value in cell A1. To extract the text, write the formula in B1.

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

      To extract the first number, write the formula in C1.

      =LEFT(A1,SEARCH(B1,A1,1)-1)

      To extract the second number, write the formula in D1.

      =RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)-1)

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Hi Alexander,

        Thank you very much for replying with a solution, it works very well.

        Is it possible to alter =RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)-1) to keep SP122202 together in a cell and not separated?

        Regards
        Andrew

        1. Hi,
          Please try the following formula:

          =B1&RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)-1)

          or

          =RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)+1)

          1. Hi,
            The second formula worked perfectly,
            Thank you very much.

            Regards
            Andrew

  14. Hi Team,

    I want below number to be extract from below given sentence.

    Q. 1 : "GL CASH DIPOSITED DONE BY ONE DATE IS 05/10/2020 02771600241 [AccountID: 123456767"

    I want the answer should be "02771600241"

    Q2: CASH DEPOSITED IN TRANSACTION ID - 02801900536 [AccountID: 1257895333 Account Name: Cas

    I want the answer should be " 02801900536 "

    1. Hi,
      If I got you right, the formula below will help you with your task:

      =TRIM(RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH(" [",A1,1)),"-",REPT(" ",20)),20))," ",REPT(" ",20)),20))

  15. What if I have multiple potential phone numbers in a free-form field and want them to extract, but be separated by a delimiter? Working from a DB extract where the most useful phone numbers are entered free-form with other miscellaneous tidbits like this:

    UserName 1234567890 Location 0987654321 OtherInfo
    OtherInfo 1234567890 Location
    M 1234567890 C 0987654321
    Location 1234567890 0987654321

    While your formula works great for extracting the numbers, it's resulting in strings like this now 12345678900987654321, which I would then need to split back up. Not all #s are 10 digits. Some have only 7 and others are international.

  16. Hi,
    I have used your formula above for extracting numbers from the left of a string [=LEFT(C738,SUM(LEN(C738)-LEN(SUBSTITUTE(C738,{"0","1","2","3","4","5","6","7","8","9"},""))))] but it is not returning the expected result:

    * String - 198503_NA_ST17 9UQ

    * Expected result - 198503

    * Actual result - 198503_NA

    If you could give me any indication as to where I have gone wrong it would be very much appreciated.
    Kind regards,
    Matt

    1. Hello!
      Please try the following formula:

      =LEFT(A2,MATCH(FALSE,ISNUMBER(--MID(A2,ROW($1:$94),1)),0)-1)

      Hope this is what you need.

      1. Hi Alexander,
        Thanks for your help but unfortunately that is returning #N/A.

        I changed the cell reference to C113 to suit where I am extracting the data from (I am extracting it into cell A113) and changed ROW references to $5:$475 as those are the rows my full data set sits in.

        Have I gone wrong somewhere making those changes? I tried it without changing the ROW references but it still returns #N/A.

        Thanks again for your help.
        Matt

        1. Hi,
          No need to change absolute references.

          =LEFT(C113,MATCH(FALSE,ISNUMBER(--MID(C113,ROW($1:$94),1)),0)-1)

          If you are using Excel 2019 and below, enter this formula as an array formula. In Excel 365, you can type as usual using the Enter key.

  17. Hello, plz
    Can anybody help me out.

    I have 26(4),5(7),9(10) in A1.

    I want to extract the numbers like this:

    26 in B1
    4 in C1
    5 in D1
    7 in E1
    9 in F1
    10 in G1

    Plz Help.

  18. Hi, I used the formula to extract number from beginning of strings :

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

    to extract

    2A 1234521

    it was supposed to extract 2, but instead it extract

    2A 12345

    Why is that? Please help.

    1. Hi Shay,

      To extract a number only from the beginning, please use this formula:

      =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&LEN(A2)+1)), 1) *1), 0) -1)

  19. Hello,
    I would like to extract the phone numbers from this cell.

    7. UZOUKWU, PRINCE ROYCE 0803 743 5119-MUM/0803 275 9140-DAD

    I have a long spreadsheet of names & the positioning of the phone numbers are not in the same place.

    However I will separate these phone numbers in 2 cells.

    1. Hello!
      If the phone number always has the same number of digits, you can try these formulas:

      =MID(A1,SEARCH("-",A1,1)-13,13)

      =MID(A1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-13,13)

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. The first one worked, for the first phone numbers and the 2nd pulled the 2nd phone number.
        Thank you so much.

  20. Hi alexander,
    How do i extract number from

    1 - 123, Singh Petrol Pump, Bishrampur, 497226, 36
    2 - Company, 123, 123, 788031, 123
    3 - 234, Danapur Maruti Suzuki Agency, Gopalganj, 841427, Bihar
    4 - Plot No RM-126,R & C Zone,, MIDC INDL. Area, Butibori. Dist Nagpur, 441122, 27- Maharashtra

    FOR 1ST ROW I WANT 497226
    FOR 2ND ROW I WANT 788031
    FOR 3RD ROW I WANT 841427
    FOR 4TH ROW I WANT 441122

    Please let me know the formula

    1. Hello!
      You are using commas as word separators. You can extract the penultimate word using the formula —

      =TRIM(MID(A1,FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)-1 -LEN(SUBSTITUTE(A1,",",""))),1)+1, FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)- LEN(SUBSTITUTE(A1,",",""))),1)- FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)-1 -LEN(SUBSTITUTE(A1,",",""))),1)-1))

      Hope this is what you need.

      1. what if i want to get only 4 digit for example :
        aadfnmm kadflk ZZ56 ladkkfiiss
        alkliid kalkem 23 lsd 5675 llk,slkdk

        thanks you

      2. Thank you Alexander

  21. ILH-E-AC-030
    ILH-E-AC-031
    ILH-E-AC-032
    ILH-E-AC-033
    ILH-E-LO-003 SHT1
    ILH-E-LO-003 SHT2
    ILH-E-LO-027 SHT1
    ILH-E-LO-027 SHT2
    ILH-E-LO-027 SHT3

    i want to extract this to other cell so it look like:
    030
    031
    032
    033
    033
    033
    027
    027
    027

    can someone tell me the formula to extract just the 3 digits number after the last "-" from left?

  22. This Formula is working out for me. But is there any solution that I can sumup the values.

    Example: 1apple&2orange = 12 (The answer what I am getting as of now but I need to sumup & get "3" as a answer)

    Please help me with this.

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

    1. Hello!
      To extract all numbers from text please use the following formula

      =CONCAT(IF(ISNUMBER(--MID(A4,ROW($1:$93),1)),MID(A4,ROW($1:$93),1),""))

      1. Working Fine.
        =CONCAT(IF(ISNUMBER(--MID(A4,ROW($1:$93),1)),MID(A4,ROW($1:$93),1),""))

        Using the formula
        1orrange&2apple = 12 ( Answer getting now)

        I need the answer as
        1orrange&2apple = 3 ( it suppose to add up the numbers)

        1. Hello!
          Replace CONCAT function with SUM:

          =SUM((IF(ISNUMBER(--MID(A4,ROW($1:$93),1)),--MID(A4,ROW($1:$93),1),"")))

          Hope this is what you need.

  23. 500-555-0172
    325-555-0137
    582-555-0148
    1 (21) 500 555-0145
    1 (12) 500 555-0117
    615-555-0153
    926-555-0182
    1 (22) 500 555-0140
    1 (11) 500 555-0190
    961-555-0122
    740-555-0182
    775-555-0164

    Write a formula to extract the numbers, eliminating all the spaces symbols state codes

    1. Hello!
      Formula to extract the numbers, eliminating all the spaces symbols and codes —

      =CONCAT(IF(ISNUMBER(--MID(REPLACE(A2,1,IFERROR(FIND(")",A2,1),1),""), ROW($1:$93),1)), MID(REPLACE(A2,1,IFERROR(FIND(")",A2,1),1),""), ROW($1:$93),1),""))

      I hope my advice will help you solve your task.

  24. Please could you help me with a formula that can extract number from 9 year(s), 11 month(s),
    and add a decimal point after years.
    Q1- 9 year(s), 11 month(s),
    Answer from formula - 9.11

    1. Hello!
      The formula below will do the trick for you:

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

      I hope it’ll be helpful.

  25. Dear Expert Users
    Please help anybody for get area from 250x350 that is written in one cell
    and area should be 87500.

    1. Hello!
      Unfortunately, you can only turn text into a formula in Excel using macros. This cannot be done using formulas.

  26. I have read well on how to extract numbers from the beginning of a text string.
    However, even if there are additional numbers in the middle of the text string, I want to extract only the characters at the beginning in addition to the additional numbers. In other words, if you have a number in the middle of a text string (if the number ends and there is another number after the letter), you want the result to remain unchanged, but the formula provided does not. Is there a possible formula?

    1. Like below
      25600aaa bbb/25*35*46cm

  27. Hi alexander,
    How do i extract number from 113°53'42" to 1135342 ?
    Please let me know the formula
    Thanks before.

    1. Hello,
      Please try the following formula:

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"”",""),"°",""),"’","")

      I hope this will help

  28. Dear
    for example : 25,20,15,25,300,40 is it possible to extract the numbers before "g",
    Ali Baba Dark Chocolate 25 gm box 12 pcs
    Ali Baba Dark Chocolate 20gm*24 box
    Cadbury 5 Star White Chocolate 15gm
    Kinder 2 White Chocolate 25 gm*24
    ALpella Biscuits W/Marshmallow300gm
    Alpella Chocolate 40gm
    plz let me know the formula

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =CONCAT(IF(ISNUMBER(--MID(MID(A15, FIND("g",A15,1)-5,5),ROW($1:$93),1)), MID(MID(A15,FIND("g",A15,1)-5,5),ROW($1:$93),1),""))

      I hope it’ll be helpful.

  29. hi there,
    how do i extract any number before a decimal point using a formula.
    meaning 5569.9008 i only want to extract 5569. the formula has to be across for any types of decimals and combination numbers. thank you for the assistance.

    1. Hello!
      For decimal use the INT function

      =INT(A1)

      What is the combination numbers? Google does not know. Neither do I.

  30. very helpful but please make practice sample files available.

    1. Hi!
      You can find the practice sample workbook at the end of this tutorial under "Available downloads".

  31. I want o extract text from number like:
    1. 100Rte02T------RTet
    how can i do that by using formula

    1. Hello Learner!
      To extract all letters from text, use the formula

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

      Hope this is what you need.

  32. Hi Team,
    -6.135474.10.00.100012-AziziDevelopments-WO-1-73944857464-CONTR0067799835-Inet

    I want to extract only this portion "6.135474.10.00.100012" and some thing like that number from rest of data of 3000. Can anyone help me please with the formula.

    1. Hello Mayank!
      If I understand your task correctly, the following formula should work for you

      =LEFT(A1,SEARCH("-",A9,2)-1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Many Many Thanks Alexander. Will try to implement with this new formula.

  33. Hi All,
    Can you please help me extract this six digit number.

    clg:ramanlal/chennai/012345/April

    1. hello Nitin!
      To extract a 6-digit number from a mext, use the formula

      =MID(A1,MATCH(0, --ISERROR(-MID(A1,ROW($1:$99),1)),),6)

      I hope it’ll be helpful.

  34. what is the appropirate formula to find mid value (i.e. 602969) of FP:ADBL5-602969-2830 starting from "FP" among the spread sheet.

    1. Hello!
      If I understand your task correctly, please try the following formula:

      =MID(A1,FIND("-",A1,1)+1, FIND("-",REPLACE(A1, FIND("-",A1,1),1,""),1)+1 -FIND("-",A1,1)-1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  35. how can i extract set of 6 number form the below string
    "LBS 28 Marg, Bhandup West, Mumbai 400078, Maharashtra"

    1. Hello Vinay!
      To extract all numbers from text, use the formula

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

      I hope it’ll be helpful.

  36. Hi, could someone please help?
    trying to extract the size from the following:
    KIERRASTONE ASH TEXTURED ZKI2655A 300 X 600 X 9MM
    (300 X 600 X 9MM)
    i dont want the numebrs with the text (zki2655a) only the 300 X 600 X 9MM
    thanks look forward to your reply :)

    1. Hello Peter!
      If I understand your task correctly, the following formula should work for you:

      =MID(A1,FIND("(",A1,1)+1,LEN(A1)-FIND("(",A1,1)-1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  37. Hi How do I find the MAX numerical value of the alphanumeric string? for example:
    X-0100
    B-0213
    F-0505
    Z-0111
    to show that F-0505 is the high value in the column

    1. Hello Kevin!
      If I understand your task correctly, the following formula should work for you

      =INDEX(A1:A5,MATCH("*"&LARGE( --RIGHT(A1:A5,LEN(A1:A5) - FIND("-",A1:A5)),1),A1:A5,0))

      If there is anything else I can help you with, please let me know.

  38. Thanks for the formulas (But I had trouble get them working. Here is why)
    In some countries, Sweden among them, the "," character is a decimal delimiter. There for "SEARCH({0,1,2,3,4,5,6,7,8,9},A2)" results in an error. So for us we have to use another character in the syntax, ";". So here is what worked for me:
    SEARCH({0;1;2;3;4;5;6;7;8;9},A2)

    Hope above saves some time for others!

    1. if character is more then 10 and less then 1000 which formula use

  39. This formula does not give the decimal values i.e 5.25, 7.3 and more. Kindly help me out on this.

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

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

  40. Not sure how to extract check no’s. From a text string having more that one set of numbers - see example below:

    Brad James Company - Check - 23897 / invoice # 456755 issued Sept 1

    Any suggestions are greatly appreciated - the line above is a sample of the excel items and after the check number shown there a number of invoice numbers in the text string.

    Thanks , Fred

    1. If this is the data :
      My Assumption is that all your data has "/" after the check number.
      First :
      Find the nth place of that "/" in that string using this formula :
      =+FIND("/",D2)
      Brad James Company - Check - 23897 / invoice # 456755 issued Sept 1
      Output = 36
      then,
      use this formula =+MID(D2,C2-7,7)
      here D2 is the input data which you have & C2 refers to the output of find formula i.e)36
      then the output will be "23987".
      Hope this helps! :)

  41. Client Name
    LALITA
    GEETA DEVI NAYAK
    MEHARUN NISHA
    DIPA MANOJ
    PREETI SINGHAL
    meena devi swami
    RAJIYA BEGAM
    SHEHIDE
    TARAWATI
    BHATERI DEVI
    sheela devi
    JANKI DEVI
    SUNITA
    ALKA KANWAR
    JAITUN
    POOJA DEBI
    CHHOTI DEVI
    VIMLA DEVI
    manju devi tak
    MANJU
    MUNNI DEVI
    GEETA DEVI
    TULSI DEVI
    AILARAKHI
    MUMTAJ BIBI
    How to Find MID name if mid name more than 3 Character

    1. =IF((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))>1,MID(A2,FIND(" ",A2,1)+1,SUM(FIND(" ",A2,FIND(" ",A2,1)+1),(FIND(" ",A2,1)+1)*-1)),"")

  42. Ram mobile no-9925923457. Resides In Noida 119961
    Can You suggest How To find Phn no. From Above Text

    1. According to your para, find the number first, once you get all the numeric, then take the left 10 digits, using the left formula.

  43. hello cen somebody please write me code for extract last 4 digits (0470) before P in serila number 1908910470P46363902R77391

    1. Hi Aljaž,

      Try this formula, where A2 is the serial number:
      =MID(A2, SEARCH("p",A2) - 4, 4)

      1. Hi Svetlana,
        Thanks for the tip but when you write a formula to my table, it return error.
        Any suggestions?

        1. Ok, I found problem. , needs to be swich for ; and then it works correctly.

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

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

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

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

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

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

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

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