Can you extract numbers from text – homework

Posted on October 30th, 2015 in Excel Challenges , Learn Excel - 112 comments

Here is a quick homework to keep you busy this weekend.

Can you extract number of days from below text.

Nov15 PUTS (23 days)
March15 TIKS (3 days)
March1 TIKS (25 days)
June11 TIKS (10 days)

Assume the data is from cell A1.

Your solution should return the following:
23
3
25
10

Post your answers (formulas, VBA code or Power Query M code) in the comments.

Thanks to Kimdom for sending this question.

If you think this is too easy, here is a twist:

Try to solve this using formulas, but you are not allowed to use FIND or SEARCH formulas.

So go ahead and post your answers.

Flex your Excel muscles, solve these problems too:

Check out these Excel problems and challenges to workout that big fat Excel muscle in your brain.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

112 Responses to “Can you extract numbers from text – homework”

  1. Michael (Micky) Avidan says:

    =SUBSTITUTE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)," days",)

  2. CookieRevised says:

    =VALUE(MID(A1,FIND("(",A1)+1,2))
    note: assumes there is always a space after a single digit number, and number is in no case no longer than 2 digits.

    Without using FIND:
    =VALUE(-RIGHT(SUBSTITUTE(A1,"days",""),5))
    note: assumes format is always "(x days)", where x is a number

    • XLarium says:

      My approach:
      =-RIGHT(SUBSTITUTE(A1," days",""),4)
      With a maximum of 99 days.

      A more flexible formula:
      =AVERAGE(IFERROR(-RIGHT(SUBSTITUTE(A1," days",""),ROW($1:$10)),""))

      • XLarium says:

        The last formula is an array formula, of course.

      • Chandoo says:

        Awesome use of - and ()s. Thanks for sharing it.

      • torkunc says:

        hi xlarium,

        regarding yr first formula, when I wirte it without minus (-) in front of RIGHT (), it gives me "ays)" as a result.

        but when I put minus, as you did, it gives #Value error.

        Can you please clarify ?

        • CookieRevised says:

          There is a space before the word 'days' in his formula, that's crucial...

          =-RIGHT(SUBSTITUTE(A1,”days”,””),4)

          The reason why you got the error #VALUE with the minus sign (if you omitted the space before 'days') is because "ays)" is not a number, obviously, and thus can not be converted into a real numerical value.

          • CookieRevised says:

            sorry, the comment system messed up my previous message....

            His formule is:
            =-RIGHT(SUBSTITUTE(A1,”[ONE SPACE HERE]days”,””),4)

            😉

          • CookieRevised says:

            My formula doesn't need the space in the formula, but it does take 5 characters (thus including the space) instead of 4 with xlarium's variation...

            The minus sign makes the negative number (Excel considers numbers between brackets being negative) a positive number....

            My formula does:
            "blahblah (23 days)" => remove "days" => "blahblah (23[SPACE])" => grab last 5 characters => "(23[SPACE])" => convert negative number to positive numerical value => 23

            xlarium's does:
            "blahblah (23 days)" => remove "[SPACE]days" => "blahblah (23)" => grab last 4 characters => "(23)" => convert negative number to positive numerical value => 23

      • Oladapo- Nigeria says:

        Thanks for sharing the minus (-) sign and () concept. I think excel is not just about the formular but a great deal of the logic to apply in solving your problem.

        As a matter of fact, FLASH FILL gave no sweat!

        Cheers all!

  3. MF says:

    My formula attempt:
    =SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),20)),"(","")," days)","")

  4. Abhay says:

    You can used Delimiter (Text to Columns) two times:
    1. Delimit using option Other - (
    2. Delimit using Space.

    Much more easier than cracking the head in writing the formulas.

  5. Abhay says:

    Formula:
    =SUBSTITUTE(LEFT(RIGHT(Text,8),2),"(","")

  6. Dheeraj says:

    My formula:

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

    Note: 1. FIND("(",A1)+1 is the cursor position where "(" ends and number starts
    2. Substitute replaces 3rd space with a star(*) and finds its position.

  7. Aniket says:

    =IF(ISNUMBER(VALUE(MID(A1,LEN(A1)-7,2))),VALUE(MID(A1,LEN(A1)-7,2)),VALUE(MID(A1,LEN(A1)-6,2)))

  8. Michael (Micky) Avidan says:

    Another approach: =MID(A1,FIND("(",A1)+1,FIND(" days)",A1)-FIND("(",A1)-1)

  9. Andi says:

    Similar to a question that came up on Superuser yesterday.
    http://superuser.com/questions/993442/how-can-i-sum-a-column-with-cells-containing-text/993446#993446

    This should work:

    =NPV(-0.9,,IFERROR(MID(RIGHT(A1,8),1+LEN(RIGHT(A1,8))-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))

  10. milang says:

    =VALUE( MID( [@Data]; SEARCH( " ("; [@Data]) + 2; SEARCH( " days)"; [@Data]) - SEARCH( " ("; [@Data]) - 2))

  11. Villalobos says:

    VBA approach from Rick Rothstein:

    Function SumParens(S As String) As Variant
    Dim X As Long, Parts() As String
    If Len(S) Then
    Parts = Split(Replace(S, ")", "("), "(")
    For X = 1 To UBound(Parts)
    SumParens = SumParens + Val(Parts(X))
    Next
    Else
    SumParens = ""
    End If
    End Function

    http://www.mrexcel.com/forum/excel-questions/56329-finding-value-between-parentheses.html

  12. Crisu says:

    For the quick approach I would copy the column, then in that copied column use Find and replace doing two things:
    - replace *( with nothing and then
    - replace "space"* with nothing.

  13. Saurabh says:

    =VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(Text," days)",),"(",),2))

  14. Olvier says:

    =IF(LEFT(RIGHT(A1,8),1)="(",VALUE(LEFT(RIGHT(A1,7),1)),VALUE(LEFT(RIGHT(A1,8),2))

  15. April Du says:

    Function getDays(myCell As Range)

    n = InStr(myCell.Value, "(")
    m = InStrRev(myCell.Value, " ")
    getDays = Mid(myCell.Value, n + 1, m - n - 1)

    End Function

  16. Jojek says:

    Quite universal formula as long as there is only one number in the string, we would like to extract - found on MrExcel probably 🙂 :

    =SUMPRODUCT(MID(0&A1;LARGE(INDEX(ISNUMBER(--MID(A1;ROW($1:$25);1))*ROW($1:$25);0);ROW($1:$25))+1;1)*10^ROW($1:$25)/10)

    • CookieRevised says:

      Yes, of course: LARGE()...doh! Thanks Jojek!
      I have some sheet where I was looking to do something like that, but couldn't figure out how... function LARGE() is the answer 😀

      But I don't get it why INDEX(x;0) is used in this formula? Doesn't it return exactly the same x matrix in this case?

      Also using SUMPRODUCT() while you do the actual multiplying yourself (because you need to convert the text numbers to real numbers) seems overkill. SUM() should be working just fine in that case (and in theory faster).

      So, this should be doing the same thing, but slightly shorter:
      =SUM(MID(0&A1;LARGE(ISNUMBER(–MID(A1;ROW($1:$25);1))*ROW($1:$25);ROW($1:$25))+1;1)*10^ROW($1:$25)/10)

  17. JeanMarc says:

    A VBA solution using Regular Expression :

    Function ExtractNumber(str As String)
    With CreateObject("vbscript.regexp")
    .Pattern = "^(.*)(\()(\d+)(.*)"
    ExtractNumber = IIf(.test(str), .Replace(str, "$3"), "")
    End With
    End Function

    • jomili says:

      JeanMarc,
      I'm not able to get your function work. I'm using it like this: "=ExtractNumber(B7)" because my data range starts in B7. I'm getting no output. Any ideas?

      • CookieRevised says:

        It works perfectly, but:

        1) He uses late binding, so first make sure that "vbscript.regexp" is a registered library on your computer.

        2) Also, the function needs to be in a module (not in the VBA code of the sheet or workbook).

        3) Make sure you change all the parenthesis to proper parenthesis in your code. Just copy/pasting from this blog will not work.

        😉

  18. Chandeep Chhabra says:

    =VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,RIGHT(A1,6),""),"(",""),2))

  19. Harry says:

    Hilariously 90% of these formulas are completely wrong.

  20. Harry says:

    =LEFT(REPLACE(A1,1,FIND("(",A1,1),""),2)

    That works 100% of the time.

  21. Jason says:

    =LEFT(RIGHT(A1,(LEN(A1)-FIND("(",A1))),LEN(RIGHT(A1,(LEN(A1)-FIND("(",A1))))-6)

  22. Mike H says:

    Simple solution without the need for any formulas etc.

    Highlight the four cells A1:A4 then use Text to Columns with delimiters of Space and Other of Open Brackets - click on finish and in column C will be the numbers as required.

    Enjoy :¬)

  23. Sachidanand Singh says:

    =IF(ISERROR(LEFT(RIGHT(A1,8),2)*1)=FALSE,LEFT(RIGHT(A1,8),2)*1,LEFT(RIGHT(RIGHT(A1,8),7),1)*1)

    I know very crude but hope it works

  24. Ira says:

    =""&(0+RIGHT((SUBSTITUTE(SUBSTITUTE(C6," days)",""),"(","( 00000")),6))

  25. Michael Pennington says:

    With FIND:

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

    Without using FIND (Array Formula -CSE):

    =MID(A1,MATCH(40,CODE(MID(A1,ROW($1:$25),1)),0)+1,LEN(A1)-6-MATCH(40,CODE(MID(A1,ROW($1:$25),1)),0))

    In case anyone is curious, I'm looking for the left parenthetic, which has a corresponding character code of 40 by creating an array of the character codes of the text string. I'm then finding it by using match in the array instead of using the much easier FIND solution. The 6 is a reference to the length of the string " days)". The array of Row($1:$25) would need to be expanded beyond 25 if the length of the strings extended past 25, but the current string maximum length is 21, so I just kept it shorter.

    • Michael Pennington says:

      Slightly shorter version of the FIND formula:

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

      It occurred to me while writing the array formula that you don't need to find the right parenthetic, it is always in the final position. The LEN doesn't really save much space, but a bit.

  26. Samtheman says:

    =MID(A1,FIND("(",A1)+1,2)

    • CookieRevised says:

      Already posted the same thing, see second comment 😉

      PS @others: I think the challenge always lies in making a SHORT and ELEGANT solution (which works) using a function or in the other categorie: VBA-code...
      I see quite a lot of extremely complicated functions (some even only 'sort-of' work or not at all). But there is no challenge there. Anyone could make a function with 1000 subfunctions in it to do the same thing...

  27. Maria G says:

    =VALUE(MID(A1,FIND("(",A1)+1,FIND("days)",A1)-(FIND("(",A1)+1)))

  28. Ben says:

    =SUBSTITUTE(SUBSTITUTE(RIGHT(A1,9)," days)","",1),"(","",1)

  29. Ken Puls says:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, true),{"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Column1.2",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", Int64.Type}, {"Column1.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1", "Column1.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.2.1", "Output"}})
    in
    #"Renamed Columns"

  30. jason m says:

    =SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("(",A1)),CHAR(160)&"days)","")+0

  31. modeste says:

    Hello;
    my soluce :
    no FIND, no SEARCH, no Array
    but only if end of string is " days)"

    =1*(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," days)",""),"(","00000"),6))

  32. Abhishek Jain says:

    =MID(A1,FIND("(",A1,1)+1,2)

  33. Sarvesh Prasad says:

    My Formula

    RIGHT(LEFT(A1,SEARCH("(",A1,1)+2),2)

  34. Muhammad Shakeel says:

    use flashfill

  35. Bernard says:

    In Excel 2016
    Type 23 then press enter
    Type 3 then press enter
    Highlight those two cells and the two cells below it.
    Click Flash Fill
    Done.

    No Formulas, hardly any typing. Too easy.

  36. SamCal says:

    For Excel 2013 & above: FlashFill. Using the data provided I was able to extract the number of days (from any number of rows!) using just three keystrokes and 2 mouse clicks:

    In A2 enter "23", Enter. Then Fill -> FlashFill.

  37. KM Zachariah says:

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

  38. Abdul Kader says:

    Just use 2 DIGITS FLASH FILL

  39. Jeff Koenig says:

    =VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(A1, " days)", ""), "(", " "), 2))

  40. eln lafrenier says:

    =IF(ISNUMBER(LEFT(RIGHT(A1,8),2)*1),LEFT(RIGHT(A1,8),2),RIGHT(LEFT(RIGHT(A1,8),2),1))

  41. Deepak says:

    =--TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99)),"

    • Deepak says:

      sorry
      =0+TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99))," ",REPT(" ",99)),99*2),99))

      & Another one

      =-(TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99*2),99))&")")

  42. sam says:

    This works as well
    --SUBSTITUTE(RIGHT(SUBSTITUTE(A1," days)",""),2),"(","")

  43. Sevinj says:

    =mid(A1, find("(",A1)+1, len(A1)-find("(",A1)-5)

  44. Stephane says:

    Another VBA formula using Regular Expressions :

    Function JustNumberofDays(rng As Range) As integer

    Dim Regex As Object
    Dim Temp As Variant
    Dim strPattern As String

    Set Regex = CreateObject("vbscript.regexp")
    strPattern = "\d+"

    With Regex

    .pattern = strPattern
    .Global = True
    Set Temp = .Execute(rng.Value)

    End With

    JustNumberofDays = Temp.Item(1).Value

    End Function

  45. Hocine Satour says:

    Hi,

    my solution (in French):
    =STXT(A1;TROUVE("(";A1)+1;TROUVE(" days";A1)-TROUVE("(";A1)-1)

    in English, I think is: =MID(A1,FIND("(",A1)+1,FIND(" days",A1)-FIND("(",A1)-1)

    Have a nice day 🙂

  46. Chirayu says:

    =MID(SUBSTITUTE(A1," days)",""),FIND("(",A1,1)+1,100)*1

  47. Vignesh Sampath Kumar says:

    =VALUE(MID(SUBSTITUTE(A1," days)",""),FIND("(",SUBSTITUTE(A1," days",""))+1,2))

  48. jason m says:

    =SUBSTITUTE(REPLACE(A1,1,SEARCH("(",A1),""),CHAR(160)&"days)","")+0

  49. Appaji Naik says:

    =IF(LEFT(RIGHT(A2,8),1)="(",LEFT(RIGHT(A2,7),2),LEFT(RIGHT(A2,8),2))

  50. Ehtisham Ali says:

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

  51. tenzing says:

    This is probably just a fix, but here you go:

    =LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),FIND("days",RIGHT(A1,LEN(A1)-FIND("(",A1)))-1)

  52. Fred says:

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

  53. Mukesh says:

    Use simple formula =+LEFT(RIGHT(A1,8),3) , will serve the purpose.
    *A1= Nov15 PUTS (23 days)

    • CookieRevised says:

      Only if you assume that the number will always be a two digit number. And because of that, no need for grabbing the extra character after the number (the space).

      When you also replace the "(" with "", it will work for numbers lower than 10 too (and even with the special case "(1 day)").
      =+SUBSTITUTE(LEFT(RIGHT(A1,8),3),"(","")

      Of course, a max of 99 is still assumed in that case.

  54. Sachin Gupta (coolsac12) says:

    =LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("(",A1)))-1)

  55. Surjendu says:

    MID(A1,(FIND("(",A1,1)+1),2)

  56. Bhavesh says:

    Well here is my formula attempt:

    =LEFT(SUBSTITUTE(A1;LEFT(A1;FIND("(";A1));"");FIND(" ";SUBSTITUTE(A1;LEFT(A1;FIND("(";A1));""))-1)

  57. Ata says:

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

  58. Sérgio Silva says:

    hi. i'm from Portugal, so here is my solution:

    the 6 that you see at the formula, is the number os caracter to the right and is assuming that the text is always: " days)"

    =SE(É.ERRO(VALOR(DIREITA(SEG.TEXTO(A1;1;NÚM.CARACT(A1)-6);2)));DIREITA(SEG.TEXTO(A1;1;NÚM.CARACT(A1)-6);1);DIREITA(SEG.TEXTO(A1;1;NÚM.CARACT(A1)-6);2))

  59. csar says:

    =SUSTITUIR( EXTRAE(A1; LARGO(A1)-7; 1) & EXTRAE(A1; LARGO(A1)-6; 1); "("; "")

    =SUBSTITUTE( MID(A1; LEN(A1)-7; 1) & MID(A1; LEN(A1)-6; 1); "("; "")

  60. Jan says:

    Sub extract()

    Dim i As Integer
    For i = 1 To 4
    Cells(i, 2) = Mid(Cells(i, 1), InStr(1, Cells(i, 1), "(") + 1, 2)

    Next i

    End Sub

  61. Mokskie says:

    Sub extract()
    Dim i As Integer
    For i = 1 To 4

    Cells(i, 2) = Mid(Cells(i, 1), InStr(1, Cells(i, 1), "(") + 1, 2)

    Next i
    End Sub

  62. Damodaran says:

    =IFERROR(VALUE(MID(D2,LEN(D2)-7,2)),VALUE(MID(D2,LEN(D2)-6,1)))

  63. David N says:

    Here is one without FIND/SEARCH that is non-array and works for any number of digits. It also does not use hard-coded integers or counters -- as in no expectation of a fixed left/right/mid position and no ROW(1:X) limiting the number of possible characters. The VALUE function even handles the possibility of trimming some extra spaces for something like Nov15 PUTS ( 23 days) while ensuring the result is numeric.

    =VALUE(SUBSTITUTE(REPLACE(A1,1,MATCH("(",INDEX(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),0),0),""),"days)",""))

  64. f(x)dx says:

    Range("A1:A4").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Replace What:=" days*", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.Replace What:="*(", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    End Sub

  65. Khalid NGO says:

    Hi Chandoo, thanks for the good challenge.
    I found very nice solutions, most users have used FIND/SEARCH 🙁

    Here is my entry:
    =-SUBSTITUTE(RIGHT(A1,9)," days","")

  66. Khalid NGO says:

    Adding to what I posted above...
    there is a CHAR(160) before "days"

    So here the revised one:
    =-SUBSTITUTE(RIGHT(A1,9),CHAR(160)&"days","")

  67. SYED ALI says:

    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,8),"days",""),")",""),"(",""))

  68. =SUBSTITUTE(MID(A1,(SEARCH("(",A1))+1,LEN(A1))," days)","")

  69. Pramod Joshi says:

    LEFT((TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),LEN(A1)))),2)

  70. Andy BW says:

    Assume formulae is in A2

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

  71. bVs says:

    Not the most elegant but here it is:
    =IFERROR(MID(RIGHT(F4,8),1,2)*1,MID(RIGHT(F4,7),1,1)*1)

  72. VladicusN says:

    =IF(LEFT(MID(A1;((LEN(A1)-7));2))="(";MID(A1;((LEN(A1)-6));2);MID(A1;((LEN(A1)-7));2))

  73. MichaelCH says:

    =-LOOKUP(,-MID(A1,FIND("(",(A1))+1,{1,2,3,4,5}))
    work 1 - 99999 days

  74. Daniel says:

    =DIREITA(ESQUERDA(A1;LOCALIZAR("days";A1;1)-1);LOCALIZAR("days";A1;1)-LOCALIZAR("(";A1;1)-1)

  75. mukesh says:

    =(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,8),")",""),"(",""),CHAR(160)&"days",""))+0

  76. Rony says:

    In Text to columns , separation basis is as space.

  77. Anitha Reddy says:

    =MID(A1,FIND("(",A1)+1,2)

Leave a Reply