Extract the 10 digit number [formula homework]

Posted on May 6th, 2016 in Excel Challenges - 94 comments

Okay, time for another challenge.

Imagine you have some data like this. Each cell contains 3 numbers separated by line break  – CHAR(10) and you need to extract the number that is 10 digits long.


Go ahead and solve this riddle.

Click here to download sample data workbook.

Post your answers in comments. Although the title says formula homework you are welcome to post VBA, Power Query and RegEx (thru VBA) solutions too.

Want to extract more?

If you derive pleasure extracting good data from junk, you are going to love below challenges.

A note: These challenges are very interesting and can take up a lot of your time (thus make you a whole lot smarter).

94 Responses to “Extract the 10 digit number [formula homework]”

  1. Abhay says:

    Power Query Script

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replaced_Carraige = Table.ReplaceValue(Source,"#(lf)","|",Replacer.ReplaceText,{"Data"}),
    Split_Column = Table.SplitColumn(Replaced_Carraige,"Data",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"Data.1", "Data.2", "Data.3"}),
    Final_Column_Added = Table.AddColumn(Split_Column, "Length", each if Text.Length([Data.1]) >= 10 then [Data.1]
    else if Text.Length([Data.2]) >= 10 then [Data.2]
    else if Text.Length([Data.3]) >= 10 then [Data.3]
    else "NA")

    • Abhay says:

      Step 1 - Replace Value "#(If)" with "|"
      Step 2 - Split columns by "|"
      Step 3 - Add column with IF function in power query identifying column with 10 digits and taking first column having 10 digits as result.

    • PL Chan says:


      • PL Chan says:

        Oops, the first formula is wrong as I have make the wrong assumption.
        Below is the modified formula to pull the 10 digits whether is the 1st row, 2nd row or the 3rd row.


    • Donald Parish says:

      Newer version of Power Query can split by Special Characters including Line feeds. See my post from 5/7/2016.

  2. Jorge says:


  3. Worm says:

    Not exactly neat, wouldn't scale, and there's no example with the ten digit number first, but it seems to work 🙂


  4. Vaibhav says:

    If data is in cell A1 then use =IF(FIND(CHAR(10),A1)=11,LEFT(A1,10),IF(FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)-FIND(CHAR(10),A1)=11,MID(A1:A1,FIND(CHAR(10),A1)+1,10),RIGHT(A1,10)))


  5. Michael (Micky) Avidan says:

    User Defined Function (UDF)
    Function Extract_10(Str As String)
    Arr = Split(Str, Chr(10))
    For CL = 0 To UBound(Arr)
    If Len(Arr(CL)) = 10 Then Extract_10 = Arr(CL)
    End Function
    In cell C3 type: =Extract_10(B3) and copy down.
    Michael Avidan

    • Calvin says:

      HI Micky,

      it is giving me #Name? error

    • Denys Calvin says:

      This is genius! And easily generalized to extract a substring of length N (assuming there is only one) set off by a particular non-numeric separator character. Hat's off to you!

    • hbillions says:

      Hello Micky,

      I like your solution. Was wondering if it can be modified to target any 10 character starting with first 3 characters being one of 20 specific 3-characters i.e. I want any 10-character number but starting with any of the following 802,803,805,806,808,809,810 then 702,703,705...and 902,903,905...910.

      Thank you.

      • Vaibhav says:

        @hbillions you can try below UDF:

        Function Extract_10(Str As String, rng As Range)
        Dim c As Range
        Arr = Split(Str, Chr(10))

        For CL = 0 To UBound(Arr)
        If Len(Arr(CL)) = 10 Then

        For Each c In rng
        If c.Value "" Then
        If Val(Left(Arr(CL), Len(c.Value))) = c.Value Then
        Extract_10 = Arr(CL)
        Exit Function
        End If
        End If
        Next c

        End If
        End Function

        syntax is

        =Extract_10(B3, E1:E10)

        here b3 contains value & E1 to E10 contains criterion for which you want to match conditions like 802,803,805,806,808,809,810 then 702,703,705...and 902,903,905...910


  6. Vaibhav says:

    The most complicated one, if value in cell A1:

    =LOOKUP(10,LEN(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)))=1),99))),TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)))=1),99)))


  7. Vaibhav says:

    Last one from my side (probably :p), its lesser complex than last one by me

    =LOOKUP(10,LEN(TRIM(MID(SUBSTITUTE(CHAR(10)&A1,CHAR(10),REPT(" ",255)),{1,2,3}*255,255))),TRIM(MID(SUBSTITUTE(CHAR(10)&A1,CHAR(10),REPT(" ",255)),{1,2,3}*255,255)))


  8. Rudra Sharma says:

    First of all I split the data into three columns with the help of Text To Columns tool. My delimiter was char(10) or Ctrl + J. Then in another column I used formula as


    • Jeff S says:

      I like your approach - much easier to debug and to understand months later. Even if the situation called for a single formula solution, this is a good way to get a known good result for validation.

    • PJ says:

      Hi Rudra,

      Text to Columns only has Tab, Semicolon, Comma, Space, Other (which supports only one character). How you split the data?

      Thanks in advance for sharing..

      • Rudra Sharma says:

        Hey PJ
        Hadn't expected that my comment will get a reply.

        Once you clicked on 'Other Character' box, hit Ctrl + J, it forces excel to use char(10) as delimiter. I learned this trick in Chandoo's forum only but don't have the link. Thanks.

    • Chandra Mohan says:

      I suggest this formula after splitting column =LOOKUP(10,LEN(D2:F2),D2:F2)

  9. Ok, I have the correct answer and it works for 10 digits in all lines;-


    Thank you Chandoo for this lovely homework !! I just love this.

  10. Daniel H says:

    Very short, but limited to the sample:


    • XLarium says:

      Hello Daniel, your solution gives #VALUE! if then 10 digit number is the first number.
      My solution is:

      • David N says:

        This MID-SEARCH approach works with the seeming consistency of the sample values but could fail if the two shorter numbers are ever consecutive with a total of nine digits because the ? wildcard would match the CHAR(10) in between them as a valid tenth character. For example, the following input would give 223923908 as a result.


  11. La función de Michael (Micky) Avidan utilizando "the function Split in VBA" es una salida fenomenal...

    Yo realicé una mucho más larga "very large"

    Aquí esta:

    Public Function Extrae10Caracteres(Celda As Range) As Long
    If Celda.Count = 1 Then
    Dim Texto As String, Caracter10 As String
    Dim LargoTexto As Long, ValorCaracter As Long
    Dim i As Long

    Texto = VBA.CStr(Celda.Value)
    LargoTexto = VBA.Len(Texto)

    For i = 1 To LargoTexto
    ValorCaracter = VBA.Asc(VBA.Mid(Texto, i, 1))
    If (ValorCaracter = 10) And (VBA.Len(Caracter10) < 10) Then
    Caracter10 = Empty
    End If

    If (ValorCaracter 10) And (VBA.Len(Caracter10) < 10) Then
    Caracter10 = Caracter10 & VBA.Mid(Texto, i, 1)
    End If
    Next i
    Extrae10Caracteres = VBA.CLng(Caracter10)
    End If
    End Function

    In Cell C3 type: =Extrae10Caracteres(B3) and copy down.
    Regards, Andres Rojas Moncada, Colombia

  12. Brian says:

    In Power Query assuming the original data is in a table called Table1

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index1" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index1",{{"Data", type text}, {"Extract", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Data",Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv),{"Data.1", "Data.2" , "Data.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Extract"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Length([Value])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 10)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index", "Row"}, {"Value", "Extract"}})
    #"Renamed Columns"

    • Donald Parish says:

      Good approach. See my 5/7 post for similar solution. I added an Index column before the unpivot, so I could tie the split rows to the original row

  13. Karthik. G says:

    How about this

    • David N says:

      This solution is perfect for the consistency of the given samples and gets my vote being both short and creative. However, it won't work if the third number is more than 10 digits. The following example would give 9004123456 as the result.


  14. cllach says:


    Almost the same ....

  15. Vaibhav says:

    ok this seems to be shorter than last one of mine

    =LOOKUP(9E+307, --MID(A1,ROW(INDIRECT("1:" & LEN(A1) - 9)), 10))


    • David N says:

      Since this solution follows the same premise as the one from Karthik G, it also gets my vote. However, it too is susceptible to the example I showed in my reply to Karthik's post.

  16. Gary Ferguson says:

    VBA Method (with flexible number length)

    Public Function GetNumWithLen(strValue As String, numLength As Integer)
    Dim strnum As Variant
    Dim i As Integer
    strnum = Split(strValue, Chr(10), , vbBinaryCompare)
    For i = 0 To 2
    If Len(strnum(i)) = numLength Then
    GetNumWithLen = strnum(i)
    Exit Function
    End If
    GetNumWithLen = "N/A"
    End Function

    then have a formula in the cell of

  17. Vaibhav says:

    Here goes Regex solution:

    Function ExtNum(cll As String, nDig As Single, cnt As Single) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
    .Pattern = "\b\d{" & nDig & "}\b"
    .IgnoreCase = True
    .Global = True
    End With

    If regex.Test(cll) Then
    Set matches = regex.Execute(cll)
    For Each Match In matches
    i = i + 1
    If i = cnt Then
    ExtNum = Match.Value
    Exit For
    End If
    Next Match
    ExtNum = ""
    End If
    Set regex = Nothing
    End Function


    • Vaibhav says:

      syntax is =ExtNum(A1,10,1)

      here A1 is cell with value,
      10 for number of matching digits,
      1 is to get number of occurrence, i.e. 1st, 2nd, 3rd & so on


  18. Brian says:


    Scalable, works for 10-digit number in all positions (as long as it's a 3-number set). Lose the VALUE() function if you don't care about a text value being returned.

  19. dolllar says:

    Can't you just divide the number by 1,000,000 and if the result is greater than 0 and less than 1 display it?

  20. Modeste Geedee says:

    Hi, folks...
    my formula :

    assuming Each cell contains 3 numbers separated by line break – CHAR(10) and max number is 10-digit

  21. SunnyKow says:

    Since all the 10 digit numbers begin with 1 and no others contain 1, I would use =MID(B3,FIND("1",B3),10). Will only work for the sample data given.

  22. Prem Singh says:


  23. Hirapara says:

    Sub Extract10()
    Dim RawData As Variant
    Dim StrgVal As String
    Dim i, j As Integer

    For i = 3 To 8
    StrgVal = Sheet1.Cells(i, 2).Value
    RawData = Split(StrgVal, Chr(10))
    For j = 0 To UBound(RawData)
    If Len(RawData(j)) = 10 Then
    Sheet1.Cells(i, 3).Value = RawData(j)
    End If
    Next j
    Next i
    End Sub

  24. Sabeesh says:

    1. Select range and click Text to Column
    2. Check Others on Step 2 type in ALT+0010 and click finish
    3. In the next empty Column type the formula INDEX(A1:C1,MATCH(10,LEN(A1:C1),0)) and CTRL + Enter and fill down
    (Assuming data is in column A)

  25. Asheesh says:


    Here is my go...!! non array solution..


    • Vaibhav says:

      This one is cool!!

      • Michael (Micky) Avidan says:

        I still think your formula (with a minor change):
        is the coolest (so far...)

        • Asheesh says:

          Exactly...so do I

          But if I were you I would not use Indirect function to generate the sequence of numbers..I would rather use ROW($A$1:INDEX(A:A,len(B3)-9))

      • Elias says:

        @Vaibhav I think yours is cool too, but I prefer Asheesh's because is not volatile.

    • David N says:

      This is a good solution, and it works for the given samples. But it won't work for others. Daniel H offered an identical solution except for the REPT; see my reply to his post for one such case.

  26. Sunil Pandey says:


  27. Vaibhav says:

    so many grey areas & combination of solutions!!

  28. Donald Parish says:

    Used GUI to build, then modified code by hand to Merge the final step with and earlier step in the query to avoid having and extra table.

    The key was to use a newer Split By feature that let's you split by Special Character of Line Feed. But first add an Index column, so when we unpivot the Split rows, we can tie each Line within a cell to the original Row number. We than add the Length of each line as a Custom Column, filter to only keep the rows with Length 10. Finally, I join it back to original data, and add a test column to match sure I match the expected value.

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    Base = Table.TransformColumnTypes(#"Added Index",{{"Extract", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Base,"Data",Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv),{"Data.1", "Data.2", "Data.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Extract"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "Data.1", "Data.2", "Data.3"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter("Data.", QuoteStyle.Csv),{"Attribute.1", "Attribute.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute.2", "Column"}}),
    #"Inserted Text Length" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length([Value]), type number),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Length", each ([Length] = 10)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Length"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns2",{"Index"},Base,{"Index"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Data", "Extract"}, {"Data", "Extract"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded NewColumn",{"Index"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns3",{"Data", "Extract", "Value", "Column"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Column", "Line"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Match?", each [Extract] =[Value])
    #"Added Custom"

  29. Elias says:

    One more with Power Query
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DupCol = Table.DuplicateColumn(Source, "Data", "DC"),
    Split = Table.SplitColumn(DupCol,"DC",Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None),{"D1", "D2", "D3"}),
    AddCol = Table.AddColumn(Split, "Extract", each if Text.Length([D1]) = 10 then [D1] else if Text.Length([D2]) = 10 then [D2] else [D3]),
    Final = Table.RemoveColumns(AddCol,{"D1", "D2", "D3"})

  30. Denys Calvin says:

    Here's a generalized solution to search in a string ("Str") for a substring of a particular length ("Length") that is set off from other substrings by a particular character ("Char"). I conceive it as recursive calls of a user-defined function I'll call "ExtractStr" that in the example given we use as follows:


    Str = the value in the "Data" column of the example
    Length = 10
    Char = Char(10)
    and the UDF is nested once because there are 3 substrings. If there were n substrings, the UDF would be nested n-2 times.

    The UDF is:

    Public Function ExtractStr(Str As String, Length As Integer, Char As String) As String
    Dim Result As String
    Dim Pos As Integer
    Pos = InStr(Str, Char)
    If Pos < Length + 1 Then
    Result = Replace(Str, Left(Str, Pos), "")
    Result = Left(Str, Length)
    End If
    ExtractStr = Result
    End Function

  31. Ernesto says:

    Its VERY simple. The formula needed is the following:


    • Michael (Micky) Avidan says:

      Although I am not Chandoo's spokesman - he wrote VERY CLEARLY (and I quote): "you need to extract the number that is 10 digits long".
      He never mentioned a thing about the first digit of the 10 digits number being "1".

  32. Target says:

    =if(mid(b3,11,1)=char(10), left(b3,10),if(mid(b3,len(b3)-10,1)=char(10),right(b3,10)mid(b3,search(char(10),b3,1)+1,10)))

    obviously assumes only 3 sub strings, and assumes there actually is a 10 digit string (ie if there are 3 6 character strings the answer would be incorrect)

  33. DJP says:

    Hi everyone,
    An array formula (Ctrl+Shift+Enter)
    Assuming that Len of col B content always = 23 (case here) and that the 10 digits no cannot be in 1st position (according to the displayed pattern), I built:
    Cheers 🙂

  34. DJP says:

    Hi again,
    Now, the 10 digits no can be in the 1st position !
    Don't forget C+S+E

  35. Micah Dail says:

    Here's my ham-handed solution. Array entered formula. Uses ROW(INDIRECT()) to loop through the string and incrementally extract out 10 characters. Then checks if characters are number, and then returns the maximum (which should be the only) number that is 10 characters long.

    {=MAX(IFERROR(1*MID(SUBSTITUTE(B3,CHAR(10),"|"),ROW(INDIRECT("1:"&LEN(B3)-9)), 10), ""))}

    • Leonid says:

      Not sure why you need SUBSTITUTE(B3,CHAR(10),"|").
      =MAX(IFERROR(1*MID($B3,ROW($A$1:INDEX(A:A,LEN($B3)-9)), 10), "")) works for me without SUBSTITUTE.
      I like your solution, but it's based on an assumption that there are no numbers there that are lengthier than 10 digits.

  36. Nanna says:

    I would use Text to Column to get three seperate columns. Then the following formula will extra the number that is 10 characters long.


  37. Malvinder Virdi says:


    Note : First time have posted some solution on your site. But have taken lot more from your website in my regular work.

    Explanation :

    Number of Character in a Cell = =LEN(B3)
    Finding First Enter Char (10) position = SEARCH(CHAR(10),B3,1)-1
    Finding 2nd Enter Char (10) position =FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)
    Finding Left Number =LEFT(B3,SEARCH(CHAR(10),B3,1)-1)
    Finding Mid Number =MID(B3,SEARCH(CHAR(10),B3,1)-1+2,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-(SEARCH(CHAR(10),B3,1)-1+2))
    Finding Right Number =RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1))

    Then if Loop to Check the count of Character in left, Mid, Center and where ever the count is 10 show it.

    Hope it is the easy solution.

  38. nitin Verma says:

    Use with control +shift+ ENter


    Nitin Verma

  39. sam says:


    • David N says:

      Although this solution was my first thought as well, see my replies to Daniel H and Asheesh for an example case where it won't work.

  40. tim says:

    shame on me ... i went the cheap route and used flash fill.

  41. Kuldeep Mishra says:


  42. Leonid says:

    I like Sam's formula:

    -not obscure, directly looks for what was asked
    -non array
    -works with both digits and alpha characters
    -easy to adjust for other length.
    I'd suggest slightly shortened version:
    If we want to extract numbers only then

  43. David N says:

    This is a reapplication of the awesome technique Roberto Mensa contributed for the Winning Streak problem Chandoo offered in a previous post. It could be shortened a little with things like --MID(...) instead of VALUE(MID(...)) or $A$1 instead of INDEX(A:A,1), but the core of the solution would remain the same.

    Note that it is a CSE array. As far as I can tell it works no matter where the 10 digit number is located and appropriately fails if there is no 10 digit number.


  44. Paul S says:

    Not the prettiest of formulas but....


  45. Rushabh Gala says:


    This gave perfect answer.

  46. MichaelCH says:


  47. Gala says:

    Here's my answer


    A7 is my initial cell. then just drag down the cell

  48. Haz says:

    =INDEX(TRIM(MID(REPLACE(B8,CHAR(10),REPT(" ",100)),{1,100,200},100)), MATCH(10,LEN(TRIM(MID(REPLACE(B8,CHAR(10),REPT(" ",100)),{1,100,200},100))),0))

  49. Sridhar Belide says:

    When this will be answered by Chandoo?

  50. CC says:


  51. Yogendra C says:

    IF(LEN(LEFT(B3,FIND(CHAR(10),B3,1)))=10,LEFT(B3,FIND(CHAR(10),B3,1)),IF(LEN(MID(B3,FIND(CHAR(10),B3,1)+1,(FIND(CHAR(10),B3,(FIND(CHAR(10),B3,1))+1))-FIND(CHAR(10),B3,1)-1))=10,MID(B3,FIND(CHAR(10),B3,1)+1,(FIND(CHAR(10),B3,(FIND(CHAR(10),B3,1))+1))-FIND(CHAR(10),B3,1)-1),IF(LEN(MID(B3,(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1,LEN(B3)-(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1))=10,MID(B3,(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1,LEN(B3)-(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1),"No Ten Numbers Exists")))

  52. Ashish kumar says:

    Dear All,

    One Problems
    I have one data of date type like this

    Feb 15 2016
    Oct 17 2016
    Jul 15 2016
    Jun 16 2016
    Apr 30 2017
    Mar 19 2017
    Jan 10 2017

    And i want to get the data in this Format

    Desired Output in Date Format

    So please help on this

Leave a Reply