# Extract the 10 digit number [formula homework]

Posted on May 6th, 2016 in Excel Challenges - 95 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.

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

 Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non-MVP) Edition Apply Conditional Formatting using Slicers
 Written by Chandoo Tags: array formulas, homework, Learn Excel, Microsoft Excel Formulas, text processing Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

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

1. Abhay says:

Power Query Script

let
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"}),
else if Text.Length([Data.2]) >= 10 then [Data.2]
else if Text.Length([Data.3]) >= 10 then [Data.3]
else "NA")
in

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

=RIGHT(SUBSTITUTE(B3,CHAR(10),""),10)*1

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

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3)<10,RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10)))

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

=IF(FIND(CHAR(10);B3)-1=10;LEFT(B3;10);IF(FIND(CHAR(10);B3;FIND(CHAR(10);B3)+1)-FIND(CHAR(10);B3)-1=10;MID(B3;FIND(CHAR(10);B3)+1;10);RIGHT(B3;10)))

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 🙂

=IF(FIND(CHAR(10),B3)=10,LEFT(B3,10),IF((FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3))=11,MID(B3,FIND(CHAR(10),B3)+1,10),RIGHT(B3,10)))

• Michael (Micky) Avidan says:

@Worm,
I'm sure you meant: =IF(FIND(CHAR(10),B3)=10,LEFT(...

• Michael (Micky) Avidan says:

Ooooopsssss,
Just saw the length correction...

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

Cheers!!

• Worm says:

Same answer, but at least you got the first length right, unlike me 🙂

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)
Next
End Function
-------------------
In cell C3 type: =Extract_10(B3) and copy down.
=========================
Michael Avidan
ISRAEL

• 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
Next
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

Cheers!!

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

Cheers!!

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

Cheers!!

• Michael (Micky) Avidan says:

@Vaibhav
Check:
1234568790
299004
99004

• Vaibhav says:

My bad, i did not check for exception.

• Vaibhav says:

This one is updated

=INDEX(TRIM(MID(SUBSTITUTE(CHAR(10)&\$A7,CHAR(10),REPT(" ",255)),{1,2,3}*255,255)),1,MATCH(10,LEN(TRIM(MID(SUBSTITUTE(CHAR(10)&A7,CHAR(10),REPT(" ",255)),{1,2,3}*255,255))),0))

Cheers!!

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

=IF(LEN(D3)=10,D3,IF(LEN(E3)=10,E3,F3))

• 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?

• Rudra Sharma says:

Hey PJ

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

=IF((FIND(CHAR(10),B3)-1)=10,LEFT(B3,10),IF(FIND(CHAR(10),B3,(FIND(CHAR(10),B3)+1))-(FIND(CHAR(10),B3)+1)=10,MID(B3,FIND(CHAR(10),B3)+1,10),RIGHT(B3,10)))

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

10. Daniel H says:

Very short, but limited to the sample:

=MID(B3,IF(FIND(CHAR(10),B3,8)=18,8,14),10)

• XLarium says:

Hello Daniel, your solution gives #VALUE! if then 10 digit number is the first number.
My solution is:
=MID(B3,SEARCH(CHAR(10)&"??????????"&CHAR(10),CHAR(10)&B3&CHAR(10)),10)

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

2239
23908
1518028737

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

• I'm Sorry, the line code:

If (ValorCaracter 10) And (VBA.Len(Caracter10) < 10) Then

Replace:

If (ValorCaracter"
different operator"10) And (VBA.Len(Caracter10) < 10) Then

Regards

12. Brian says:

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

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"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"),
#"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"}})
in
#"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:

=LOOKUP(10^10,MID(B3,ROW(INDIRECT("1:"&LEN(B3)-9)),10)+0)

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

299004
1760481410
99004123456

14. cllach says:

=MID(B3,IF(FIND(CHAR(10),B3)=11,1,IF(FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3)=11,FIND(CHAR(10),B3)+1,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)+1)),10)

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

Cheers!!
blog.ExcelVbaLab.Com

• 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
Next
GetNumWithLen = "N/A"
End Function

then have a formula in the cell of
=GetNumWithLen(B3,10)

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
Else
ExtNum = ""
End If
Set regex = Nothing
End Function

Cheers!!
blog.ExcelVbaLab.Com

• 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

Cheers!!

18. Brian says:

=VALUE(CHOOSE(IF(FIND(CHAR(10),B4)=11,1,IF(FIND(CHAR(10),B4,FIND(CHAR(10),B4)+1)-FIND(CHAR(10),B4)=11,2,3)),LEFT(B4,10),LEFT(RIGHT(B4,LEN(B4)-FIND(CHAR(10),B4)),10),RIGHT(B4,10)))

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 :
=IF(FIND(CHAR(10),A1)>10,LEFT(A1,10),IF(CODE(RIGHT(A1,11))=10,RIGHT(A1,10),MID(A1,FIND(CHAR(10),A1)+1,10)))

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.

• SunnyKow says:

Can also use VALUE(MID(B3,FIND(1,B3),10)) if need it to convert to a value

22. Prem Singh says:

=INDEX(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),10),MATCH(TRUE,LEN(ABS(1*(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),10))))=10,0),1)

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:

Hi,

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

MID(B3,SEARCH(CHAR(10)&REPT("?",10)&CHAR(10),CHAR(10)&B3&CHAR(10)),10)

• Vaibhav says:

This one is cool!!

• Michael (Micky) Avidan says:

@Vaibhav,
I still think your formula (with a minor change):
=LOOKUP(9^99,--MID(B3,ROW(INDIRECT("1:"&LEN(B3)-9)),10))
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))

• Vaibhav says:

even though this will fetch result, but i like to keep a number of functions as less as possible.

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

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),
IF(FIND(CHAR(10),RIGHT(B3,LEN(B3)-(FIND(",",SUBSTITUTE(B3,CHAR(10),",",1)))))=11,LEFT(RIGHT(B3,LEN(B3)-(FIND(",",SUBSTITUTE(B3,CHAR(10),",",1)))),10),
RIGHT(B3,LEN(B3)-(FIND(CHAR(10),SUBSTITUTE(B3,CHAR(10),",",1),1)))))

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.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
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"}}),
in

29. Elias says:

One more with Power Query
let
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]),
in
Final

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:

=ExtractStr(ExtractStr(Str,Length,Char),Length,Char)

where
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), "")
Else
Result = Left(Str, Length)
End If
ExtractStr = Result
End Function

31. Ernesto says:

Its VERY simple. The formula needed is the following:

=MID(B3,FIND(1,B3,1),10)

• Michael (Micky) Avidan says:

@Ernesto,
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:
{=IF(MAX((MID(B3,ROW(A1:A23),1)=CHAR(10))*ROW(A1:A23))=13,RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10))}
Cheers 🙂

34. DJP says:

Hi again,
Now, the 10 digits no can be in the 1st position !
=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(MAX((MID(B3,ROW(A1:A23),1)=CHAR(10))*ROW(A1:A23))=13,RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10)))
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:

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

=IF(LEN(B3)=10,B3,IF(LEN(C3)=10,C3,D3))

37. Malvinder Virdi says:

=IF(LEN(LEFT(B3,SEARCH(CHAR(10),B3,1)-1))=10,LEFT(B3,SEARCH(CHAR(10),B3,1)-1),IF(LEN(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)))=10,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)),IF(LEN(RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)))=10,RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)))))

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

=MAX(IFERROR(VALUE(MID(B3,ROW(INDIRECT("a1:a"&LEN(B3))),10)),0))

Regards
Nitin Verma

• Vaibhav says:

Formula not working when there is number with more than 10 digit.

39. sam says:

=MID(CHAR(10)&B3&CHAR(10),SEARCH(CHAR(10)&"??????????"&CHAR(10),CHAR(10)&B3&CHAR(10))+1,10)

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

=IF(LEN(LEFT(B3,FIND(CHAR(10),B3)-1))=10,LEFT(B3,FIND(CHAR(10),B3)-1),IF(LEN(RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)))=10,RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)),MID(B3,FIND(CHAR(10),B3)+1,10)))

42. Leonid says:

I like Sam's formula:

-not obscure, directly looks for what was asked
-short
-non array
-works with both digits and alpha characters
-easy to adjust for other length.
I'd suggest slightly shortened version:
=MID(B3,SEARCH(CHAR(10)&REPT("?",10)&CHAR(10),CHAR(10)&B3&CHAR(10)),10)
If we want to extract numbers only then
=IFERROR(--MID(B3,SEARCH(CHAR(10)&REPT("?",10)&CHAR(10),CHAR(10)&B3&CHAR(10)),10),"")

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.

=MID(B3,MATCH(10,FREQUENCY(IF(ISNUMBER(VALUE(MID(B3,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))),1))),ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3)))),IF(ISNUMBER(VALUE(MID(B3,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))),1))),,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))))),0)-10,10)

44. Paul S says:

Not the prettiest of formulas but....

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3)=11,RIGHT(LEFT(B3,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)),11),RIGHT(B3,10)))

45. Rushabh Gala says:

=TEXT(MAX(IFERROR(VALUE(MID(B5,14,10)),0),IFERROR(VALUE(MID(B5,7,10)),0),IFERROR(VALUE(MID(B5,8,10)),0)),"0000000000")

46. MichaelCH says:

=MID(A1,MATCH(11,FREQUENCY(ROW(\$1:\$99),ISERR(-MID(A1&-10^9,ROW(\$1:\$99),1))*ROW(\$1:\$99)),)-10,10)

47. Gala says:

=IF(LEN(TEXT(A7,"0"))=10,A7,"")

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

• Haz says:

With CSE:
{=MID(B3,MATCH(10,LEN(REPLACE(MID(B3,ROW(OFFSET(\$A\$1,,,LEN(B3))),10),CHAR(10),)),0),10)}

49. Sridhar Belide says:

When this will be answered by Chandoo?

50. CC says:

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(ISERROR(FIND(CHAR(10),RIGHT(B3,10))),RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10)))

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

Date
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
15-Feb-16
17-Oct-16
15-Jul-16
16-Jun-16
30-Apr-17
19-Mar-17
10-Jan-17

• Michael (Micky) Avidan says:

@Ashish kumar,
Select the 7 cells range > goto 'DATA' > 'TEXT TO COLUMNS' > mark 'SEPERATED' > NEXT > NEXT > mark date format 'MDY' > 'FINISH'.
-----------------
Michael Avidan
ISRAEL

53. Rishi Kumar says:

=IF(FIND("",B4)=11,LEFT(B4,10),IF(FIND("",B4,FIND("",B4)+1)-FIND("",B4)<10,RIGHT(B4,10),MID(B4,FIND("",B4)+1,10)))

 Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non-MVP) Edition Apply Conditional Formatting using Slicers